On Thursday, October 22, 2015 at 10:46:43 AM UTC-7, James wrote:
>
> Say you have the following simplified tables and data:
>
> Category
> - id
> - parent_id
> - name
>
> Product
> - id
> - category_id
> - name
> - price
>
> Category Data
> 1, NULL, 'GPS'
> 2, 1, 'Vehicle GPS'
> 3, 1, 'Handheld GPS'
> 4, NULL, 'GPS Accessories'
>
> Products Data
> 1, 2, 'Nuvi', 99.99
> 2, 3, 'eTrex', 99.99
> 3, 3, 'Montana', 99.99
> 4, 4, 'Carrying Case', 29.99
>
> Say I want to display only top level categories (parent_id IS NULL), where
> a particular product matches a certain filter (e.g. price < $100). So
> because I'm an amateur I first try something like:
>
> SELECT categories.name
> FROM categories
> WHERE id IN (SELECT category_id FROM products WHERE price < 100.0)
> AND categories.parent_id IS NULL
>
> I quickly realize this will never work because no Products are associated
> with root category id's. If I know the exact number of levels, I could add
> another category table. However, I'm wondering if there's a way to do this
> with recursive CTE and the rcte_plugin that will work no matter how many
> levels down the products are? I have been reading up on CTE, and while I
> think I know in general what I need to do, I'm having a hard time
> understanding CTE's or how to go about this. Any guidance would be greatly
> appreciated.
>
I've tested this, it appears to work:
DB.create_table(:categories) do
Integer :id, :primary_key=>true
foreign_key :parent_id, :categories
String :name, :null=>false
end
DB[:categories].import([:id, :parent_id, :name], [[1, nil, 'GPS'], [2, 1,
'Vehicle GPS'], [3, 1, 'Handheld GPS'], [4, nil, 'GPS Accessories']])
DB.create_table(:products) do
Integer :id, :primary_key=>true
foreign_key :category_id, :categories, :null=>false
String :name, :null=>false
Float :price, :null=>false
end
DB[:products].import([:id, :category_id, :name, :price], [[1, 2, 'Nuvi',
99.99], [2, 3, 'eTrex', 99.99], [3, 3, 'Montana', 99.99], [4, 4, 'Carrying
Case', 29.99]])
cte = DB[:c].
with_recursive(:c,
DB[:categories].
select(:id, :parent_id).
where(:id=>DB[:products].where{price < 100.0}.select(:category_id)),
DB[:c].join(:categories, :id=>:parent_id).select(:categories__id,
:categories__parent_id))
DB[:categories].
where(:parent_id=>nil).
where(:id=>cte.select(:id))
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
