drewolson/scrivener_ecto

Error when trying to paginate with intersect or intersect_all

ajose01 opened this issue · 0 comments

I have the following query:

def filter(or_tags, and_tags) do
    or_query = or_query(or_tags)
    num = length(and_tags)
    from record in Record,
      join: recordtag in RecordTag, on: recordtag.record_id == record.id,
      join: tag in Tag, on: tag.id == recordtag.tag_id,
      where: tag.name in ^and_tags,
      group_by: record.id,
      having: count(tag.name, :distinct) == ^num,
      intersect: ^or_query,
      select: record
  end

def or_query(tags) do
    from record in Record,
      join: recordtag in RecordTag, on: recordtag.record_id == record.id,
      join: tag in Tag, on: tag.id == recordtag.tag_id,
      where: tag.name in ^tags,
      group_by: record.id,
      select: record
  end

When I try to paginate, I get the following error:
ERROR 42601 (syntax_error) each INTERSECT query must have the same number of columns
The produced query:

query: SELECT count('*') FROM (SELECT r0."id" AS "id" FROM "records" AS r0 INNER JOIN "record_tags" AS r1 ON r1."record_id" = r0."id" INNER JOIN "tags" AS t2 ON t2."id" = r1."tag_id" WHERE (t2."name" = ANY($1)) GROUP BY r0."id" HAVING (count(DISTINCT t2."name") = $2) INTERSECT (SELECT r0."id", r0."address1", r0."address2", r0."address3", r0."cert_end", r0."cert_id", r0."cert_start", r0."cert_type", r0."city", r0."country", r0."dba1", r0."dba_other", r0."email", r0."fax", r0."first_name", r0."last_name", r0."legal_name", r0."license", r0."naics", r0."phone", r0."postal_code", r0."state", r0."url", r0."inserted_at", r0."updated_at" FROM "records" AS r0 INNER JOIN "record_tags" AS r1 ON r1."record_id" = r0."id" INNER JOIN "tags" AS t2 ON t2."id" = r1."tag_id" WHERE (t2."name" = ANY($3)) GROUP BY r0."id")) AS s0

If I use a JOIN and subquery vs. intersect pagination works, however I take a large performance hit.

Any clues on how I can get this to work? Much appreciated!