0

User model :

Class User < ActiveRecord::Base
  has_many :planning_entries

PlanningEntry model :

Class PlanningEntry < ActiveRecord::Base
  belongs_to :planning
  belongs_to :user
  has_many :plannings

Planning model

Class Planning < ActiveRecord::Base
  has_many :planning_entries
  • The planning table has a field day:date
  • The PlanningEntry has a field state: [:approved, :canceled, etc.]

I would like to make a scope to retrieve all users not assigned to the current day's Planning using the day field.

This mean that i can't exclude users having planning_entries from old plannings

On other thing, there are several states for the PlanningEntry table and only the :approved state is considered as a true association for a user to a planning in this request

So far i did this query : 

 User.joins( planning_entries: :planning ).
    where( "(plannings.day = ? AND planning_entries.state != 'approved')",
    Planning.current_day)

I tried to add some more OR / AND condition in the query to deal with all the cases needed but nothing worked so far.

Any help would be grandly appreciated

C404
  • 243
  • 4
  • 14
  • 1
    I tried and failed to parse this sentence: `a user who has a planning_entry with everything but approved state`. I also failed to find your version of Postgres. And `the current day's Planning`? Is `day` supposed to be unique / primary key in `planning`? Can you clarify? – Erwin Brandstetter Apr 24 '15 at 00:39
  • @ErwinBrandstetter sorry for my english. I meant that there are several states for the PlanningEntry table and only the `:approved` state is considered as a true association for a user to a planning in this request. Hope it was clearer :) – C404 Apr 24 '15 at 08:37
  • Please *edit the question* to clarify. Shouldn't be hidden in comments only. – Erwin Brandstetter Apr 24 '15 at 09:01
  • `Query to find records having 0 nested association` ... Syntax Error in line#0. – joop Apr 24 '15 at 09:16

1 Answers1

0

I would like to ... retrieve all users not assigned to the current day's Planning.

In SQL, one of various ways:

SELECT *
FROM   Users u
WHERE  NOT EXISTS (
   SELECT 1
   FROM   Planning p
   JOIN   PlanningEntry pe ON pe.planning_id = p.id
   WHERE  p.day = CURRENT_DATE
   AND    pe.users_id = u.id
   AND    state = 'approved'  -- only 'approved' matters
   );

This will return all users except (the few?) that are approved for (any of) today's meeting(s).

I am guessing columns names you did not disclose.

You may have to double-quote identifiers if you obfuscation layer is in the habbit of double-quoting mixed case identifiers.

More:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228