Query on non-match across a join

Stumped with authoring a query for a use case. Here’s the layout:
Tables: Users, Votes, Items, Events
An event has multiple items. A user votes on items in an event. Votes are upserted per item and recorded as a boolean were true = “yes” and false = “no.”

In the event view, we make several views available to the user: All Items, Items with my Yes votes, Items with my No votes.

We now want to add a view for items in an event where the user has not yet voted. If they haven’t yet voted on any item in an event, then that result is naturally All items. That part’s sorted.

How best to query to identify items not yet touched where they have touched at least one sibling item for an event? There would be at least one True or False item in the votes table for that user and for the event. I’m not yet having luck joining on the votes table where we match on item and user and don’t match any True or False items.

join: v in Vote, where: v.user_id == ^user_id and v.event_id == ^event_id and v.vote != false and v.vote != true
1 Like

I’m not gonna grok your logic, put look at using another “join” eg left_join or right_join etc.

https://hexdocs.pm/ecto/Ecto.Query.html#join/5

2 Likes

This sounds like a left join (upper left of outlog’s table) with a WHERE condition of votes.id = nil

From what I understand it kind of seems like you’re overcomplicating this and can instead just rely on the left join logic that I outlined above. Either that or I’m not understanding the business requirement that is causing you to care about sibling items.

1 Like

This may clarify a bit. Here are some sample tables.

In this example, User #1 has voted on just one item and User #2 has voted on that same item plus two more.

I need to compose a query that will identify the items that User #1 has not yet voted on.

Other users will have voted on some of those items. A left join from items to the votes table against an is_nil match on items in the votes table will not be limited to User #1’s non-votes. Which is what I meant by a non-match – I’m looking to catalog actions that haven’t happened.

I’m beginning to think that the solution may lie in querying the items, querying the user-voted items and using an Enum filter to extract a diff of the query results. Though if there’s a way to tackle that in Ecto composition, I’d love to get a pointer on how to approach it, not having found an example yet.

Users table
| id | name | 
| 1  | Jim  | 
| 2  | Tom  | 

Items table
| id | event_id | item_name |
| 1  | 1        | thing 1   |
| 2  | 1        | thing 2   |
| 3  | 1        | thing 3   |
| 4  | 1        | thing 4   |
| 5  | 1        | thing 5   |
| 6  | 1        | thing 6   |
| 7  | 2        | thing 7   |
| 8  | 2        | thing 8   |
| 9  | 2        | thing 9   |
| 10 | 2        | thing 10  |
      
Votes table
| id | vote  | item_id | user_id | event_id |
| 1  | true  | 1       | 1       | 1        |
| 2  | false | 1       | 2       | 1        |
| 2  | true  | 4       | 2       | 1        |
| 2  | true  | 5       | 2       | 1        |

Two minor notes before getting to the query:

There’s a gotcha with code like this - if the row doesn’t exist v.vote will be NULL and both NULL != true and NULL != false evaluate to… NULL.

event_id isn’t strictly necessary here, unless a vote can somehow be attached to a different event than the corresponding item.

On the query front:

SELECT * FROM items
LEFT OUTER JOIN votes ON votes.item_id = items.id AND votes.user_id = 1

will give you one row for every row in items, but with the accompanying vote from user 1 if available, so:

SELECT * FROM items
LEFT OUTER JOIN votes ON votes.item_id = items.id AND votes.user_id = 1
WHERE votes.id IS NULL

will give you all items where’s not a corresponding vote from user 1.

Note that the positioning of the votes.user_id = 1 clause is important - as part of the ON, a failure to match any rows will result the join returning an all-NULL row. Moving votes.user_id = 1 to the WHERE will require matching rows to have votes.user_id actually set.

2 Likes

Thanks so much, @ al2o3cr! I’ve got it sorted out now.

1 Like