I have a standard ruby includes, smth. like that:
@properties = Property.where(id: property_ids)
.includes(:property_values).select('select distinct "property_values"."value"').references(:property_values)
I need the uniq values for each properties property values.
But for this query get an error:
PG::SyntaxError - ERROR: syntax error at or near "select"
LINE 1: ...S t1_r4, "property_values"."updated_at" AS t1_r5, select dis...
Here is full query:
: SELECT "properties"."id" AS t0_r0, "properties"."k1c" AS t0_r1, "properties"."name" AS t0_r2, "properties"."created_at" AS t0_r3, "properties"."updated_at" AS t0_r4, "property_values"."id" AS t1_r0, "property_values"."value" AS t1_r1, "property_values"."product_id" AS t1_r2, "property_values"."property_id" AS t1_r3, "property_values"."created_at" AS t1_r4, "property_values"."updated_at" AS t1_r5, select distinct "property_values"."value" FROM "properties" LEFT OUTER JOIN "property_values" ON "property_values"."property_id" = "properties"."id" WHERE "properties"."id" IN (159, 27, 26, 25, 24, 23, 22, 4, 1) AND "properties"."id" IN (1) ORDER BY "properties"."id" ASC
How can I avoid this error and get just uniq property values?
@properties = Property.where(id: property_ids).
joins(:property_values).
select('"property_values"."value"').
distinct.
references(:property_values)
Note I changed includes
to a join as includes
loads data in a separate query where as join
actually does a join allowing you to perform action on the data within the query..
I have used the distinct method instead of putting it within the select however you could do this but you would need to remove the word select from your string
@properties = Property.where(id: property_ids)
joins(:property_values).
select('distinct "property_values"."value"').
references(:property_values)
Read more http://blog.bigbinary.com/2013/07/01/preload-vs-eager-load-vs-joins-vs-includes.html