CODEWITHSUNDEEP
ruby-on-railsrubypostgresql
Vini.g.fer
Vini.g.fer

Reputation: 11919

Where clause filtering by "ANY" - What does this means?

I'm trying to understand why I don't receive any records on a ruby on rails app using postgresql. This is the SQL query that is being executed:

SELECT g.program_id, g.title, 
    COALESCE(COUNT(pr), 0) AS ac, g.default 
FROM groups AS g 
  LEFT OUTER JOIN memberships AS m ON m.group_id = g.id 
  LEFT OUTER JOIN progresses AS pr ON m.id = pr.participant_id 
                                  AND (pr.status = 'completed') 
WHERE g.program_id = ANY(@1)
GROUP BY g.id 
ORDER BY g.program_id, g.position, g.id

My question is: what does the ANY(@1) means?

Please have pacience, as I'm a ruby/rails/postgresql newbie.

Thanks!

Update: added some aditional code. Plese don't ident the query below as it is already idented above.

class StatsComponents::CompletedActivitiesPerGroupStats
  include StatsComponent::Interface

  GROUP_ACTIVITIES = <<-SQL
  g.program_id, g.title, COALESCE(COUNT(pr), 0) AS ac, g.default
  FROM groups AS g
  LEFT OUTER JOIN memberships AS m ON m.group_id = g.id
  LEFT OUTER JOIN progresses AS pr ON m.id = pr.participant_id
  AND (pr.status = 'completed')
  WHERE g.program_id = ANY(@1)
  GROUP BY g.id
  ORDER BY g.program_id, g.position, g.id
  SQL

  def generate
  ...

Upvotes: 2

Views: 65

Answers (1)

Eugene Petrov
Eugene Petrov

Reputation: 1598

It selects records where g.program_id has a value existing in the array returned by @1 request, which they set as a query parameter (for example SELECT...) somewhere further in the program.

You can equally use SOME(@1) here.

By the way strictly speaking, this isn't a sql query. While there is no sql.execute or something like that, it's just a multiline string assignment.

Upvotes: 1

Related Questions