Rails 4 Join Query

Question

I'm building a marketplace app on rails 4. I have a seller widget that displays a random list of sellers on our platform. Sometimes, sellers' listings expire so they don't have any items for sale. I want to exclude these sellers from appearing in the widget.

I have a User model (users can be buyers or sellers). Sellers have a profile description and image while buyers do not. So to create the widget, I use the below query.

<% User.where("profileimage_file_name != ? AND profilestory != ?", "", "").order("random()").limit(12).each do |user| %>
#some code to link to seller page
<% end %>

I have a Listing model which stores all the product listings and has a user_id as a foreign key.

The User model doesn't have any listings data. How do I write the query so I can join the User Model with the Listings model and only show Users who have at least 1 listing active.

You use a date field which holds an expiration date for the listing, correct? — Paul Richter, Oct 21 '14 at 05:47
No, I have a expiration method which calculates 30 days prior to creation date. — Moosa, Oct 21 '14 at 05:54
For example, if I were querying the listing model,I would do `<% @listings.not_expired.where(...` — Moosa, Oct 21 '14 at 06:02

score 1 · Accepted Answer · answered Oct 21 '14 at 06:10

1

A couple of ways you can do this:

@sellers = User.where("profileimage_file_name != ? AND profilestory != ?", "", "").
                joins(:listing).
                where("listings.created_at <= (created_at + INTERVAL 30 day) ").
                order("random()").limit(12)

A cleaner way:

class User < ActiveRecord::Base
    default_scope -> { order("random()")} # this is optional
    scope :sellers, -> { where("profileimage_file_name != ? AND profilestory != ?", "", "") }
    scope :with_active_listings, -> { joins(:listing).where("listings.created_at <= (created_at + INTERVAL 30 day) ")}

    ...
end

And then simply:

@sellers = User.sellers.with_active_listings.limit(12)

These are somewhat general and make a few assumptions about your system, but hopefully it makes enough sense. Let me know whether that helps.

answered Oct 21 '14 at 06:10

Paul Richter

10,908
10
52
85

**NOTE:** I posted this before seeing that you're using sqlite. The `INTERVAL` portion does not exist in that dbms. The problem is the calculation needs to be done in the DB using the way I've shown, and you can't use the rails method you mentioned. – Paul Richter Oct 21 '14 at 06:15
That's fine. I got the basic syntax. I'll figure it out from here. – Moosa Oct 21 '14 at 06:30
@Paul_Richter I'm using the query you posted above but I'm getting duplicates. How would I insert a `distinct User` filter? – Moosa Oct 24 '14 at 15:45
@Moosa Try putting `.uniq` at the end of the query, that should do it. – Paul Richter Oct 24 '14 at 16:01
@Moosa Just remembered, you could also append `.group(:id)` at the end instead. Both should work equally well, the only difference comes if you're using aggregates, or possibly subqueries. – Paul Richter Oct 24 '14 at 19:07
@Paul_Richter With both .uniq and .group(:id), it works in locahost but on heroku it gives me an error. For group(:id), the error is `PG::AmbiguousColumn: ERROR column reference "id" is ambiguous` and for uniq, th error is `for SELECT DISTINCT, ORDER BY expressions must appear in select list` – Moosa Oct 25 '14 at 06:08
@Moosa That's...weird. I have never used Heroku so I can't really advise you there unfortunately. For the group by, try being explicit about which id to group (ex: `.group('users.id')`). For the `DISTINCT` / `uniq`, yes sorry I think [this answer](http://stackoverflow.com/a/12693353/877472) has a good explanation on that. I can't explain why it works in local, and not in heroku, unfortunately. Sounds like the corrected `.group` I mentioned might be the simpler option. Let me know if that works. – Paul Richter Oct 25 '14 at 06:39
@Paul_Richter users.id worked. Thanks a lot for your help. Heroku used Postgres and it's a stricter db. – Moosa Oct 25 '14 at 16:24
@Paul_Richter is there a performance difference between using the query and using the scope option? – Moosa Oct 25 '14 at 16:49
@Moosa Postgres, ok that makes a bit more sense. And as for your performance question, do you mean what is the difference between the two solutions I posted? If so, no, there is no performance difference. The scope solution is much cleaner, and reusable / DRY. – Paul Richter Oct 25 '14 at 23:13

Rails 4 Join Query

1 Answers1