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
– Paul Richter, Commented Oct 21, 2014 at 5:47
No, I have a expiration method which calculates 30 days prior to creation date. — Moosa
– Moosa, Commented Oct 21, 2014 at 5:54
For example, if I were querying the listing model,I would do <% @listings.not_expired.where(... — Moosa
– Moosa, Commented Oct 21, 2014 at 6:02

Paul Richter · Accepted Answer · 2014-10-21 06:10:56Z

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.

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.
That's fine. I got the basic syntax. I'll figure it out from here.
@Paul_Richter I'm using the query you posted above but I'm getting duplicates. How would I insert a distinct User filter?
@Moosa Try putting .uniq at the end of the query, that should do it.
@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.

Collectives™ on Stack Overflow

Rails 4 Join Query

1 Answer 1

10 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Linked

Related