CODEWITHSUNDEEP
sqlelixirecto
Kourosh Alinaghi
Kourosh Alinaghi

Reputation: 101

Recursive SQL query with Ecto.Query

I have a categories table with the following columns:

id
name
parent_id (nullable)

And a books table which has a category_id column inside of it. I want a function that takes a list of category ids (category_ids) and returns a query that gets books which belong to one of the given categories or their children (recursive).

I've already written a query that returns all of a given category's children. I could use that to fetch all of the subcategories of category_ids categories and use the new list. But it would send several queries to the database and I want to do it in one query. Here's the query:

with recursive cat_tree as (
  select id,
    name,
    parent_id
  from categories
  where id = $1
  union all
  select child.id, 
    child.name,
    child.parent_id
  from categories as child
  join cat_tree as parent on parent.id = child.parent_id
)
select *
from cat_tree;

EDITED

  @raw_sql """
  select id,
    name,
    parent_id
  from categories
  where id in (?)
  union all
    select child.id,
           child.name,
           child.parent_id
  from categories as child
    join cat_tree as parent on parent.id = child.parent_id
  """

  def category_search_query(query, []), do: query 
  def category_search_query(query, category_ids) do
    query
    |> recursive_ctes(true)
    |> with_cte("cat_tree", as: fragment(@raw_sql, ^category_ids))
    |> join(:inner, [b], c in "cat_tree", on: c.id == b.category_id)
  end

But when I pass [12, 13] (for example) to the function, it gives me the following error:

(DBConnection.EncodeError) Postgrex expected an integer in -9223372036854775808..9223372036854775807, got '\f\r'. Please make sure the value you are passing matches the definition in your table or in your query or convert the value accordingly.

But when I pass just an integer (and not a list), it works correctly.

Upvotes: 2

Views: 416

Answers (1)

eshirvana
eshirvana

Reputation: 24568

I would make a procedure to get list of categories as parameter (can be an array) and change your query to this :

create function funcName (categoryIds int[])
returns table ( bookid int ,categoryid int , ...<deffine book columns>)
as 
$$ 
with recursive cat_tree as (
    select
        id,name,parent_id
    from
        categories
    where id in (select unnest(categoryIds))
    union all
    select
        child.id,child.name,child.parent_id
    from
        categories as child
    join cat_tree as parent on parent.id = child.parent_id
)
select
    b.*
from
    cat_tree c
join books on c.id = b.categoryid
$$ Language sql;

Upvotes: 3

Related Questions