On Thursday, October 22, 2015 at 12:30:57 PM UTC-6, Jeremy Evans wrote:
>
>
>
> 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
>
Awesome, thank you very much Jeremy. I will give this a try!
James
--
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.
