On Thursday, November 5, 2015 at 12:14:32 PM UTC-8, flyer wrote:
>
> Hi, I am struggling a lot trying to translate this query into Sequel DSL.
> Suppose I have the following table schemas (only showing meaningful
> columns):
>
> Table "public.products"
> Column | Type |
> Modifiers
>
> ---------------------+-----------------------------+-------------------------------------------------------
> id | integer | not null default
> nextval('products_id_seq'::regclass)
> name | character varying(45)
> product_category_id | integer |
>
>
> Table "public.product_categories"
> Column | Type |
> Modifiers
>
> -----------------------+------------------------+-----------------------------------------------------------------
> id | integer | not null default nextval
> ('product_categories_id_seq'::regclass)
> name | character varying(255)
> parent_id | integer |
>
> So products can have categories, and categories can have subcategories.
> Moreover products may belong to a parent category and not necessarily to
> subcategories. So suppose I have the following categories and products data:
>
> Categories Table
>
> id name parent_id
> 1 Drinks
> 2 Starters
> 3 Alcohol 1
> 4 Without Alcohol 1
> 5 Cold 2
> 6 Hot 2
>
> Products Table
>
>
> id name product_category_id
> 1 Coca-Cola 4
> 2 Water 4
> 3 Wine 3
> 4 Beer 3
> 5 Orange Juice 1
> 6 Picada Chica 2
> 7 Muzz Milanesa 6
> 8 Salpicon Ave 5
>
> So notice that some products just belong to "main_categories". What I want
> to do is return a set ordered alphabetically first by "main_category", then
> by "subcategory" and finally by product name. I was able to do this in
> plain SQL executing the following query:
>
> SELECT p.name, p.id, p.product_category_id, ij.main_category, ij.subcategory
> FROM
> products p inner join
> (select pc.id, coalesce(pcc.name, pc.name) main_category, case when
> pcc.name
> is not null THEN pc.name end subcategory FROM product_categories pc left
> join product_categories pcc on pc.parent_id = pcc.id) ij on
> p.product_category_id
> = ij.id order by ij.main_category, ij.subcategory nulls first, p.name;
>
Assuming you are using models, I think this will work:
Product.many_to_one :category
Category.many_to_one :parent
Product.association_left_join(:category=>:parent).
exclude(:category__id=>nil).
select(:product__name, :product__id, :product__product_category_id,
:parent__name___main_category, :category__name___subcategory).
order(:parent__name___main_category, :category__name___subcategory,
:product__name)
It works differently from your query, as it doesn't join to a subquery.
Not sure which performs better, you'd have to benchmark. If you want a
more direct translation of the subquery approach that doesn't use model
associations, please let me know.
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.
