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;
Executing this query will return the following:
name | id | product_category_id |
main_category | subcategory
----------------------------------------+----+---------------------+--------------------+-------------
Orange Juice | 5 | 1 | Drinks
|
Beer | 4 | 3 | Drinks
| Alcohol
Wine | 3 | 3 | Drinks
| Alcohol
Coca-Cola | 1 | 4 | Drinks
| Without Alcohol
Water | 2 | 4 | Drinks
| Without Alcohol
Picada Chica | 6 | 2 | Starters
|
Salpicon Ave | 8 | 5 | Starters
| Cold
Muzz Milanesa | 7 | 6 | Starters
| Hot
As you can see, the wanted order is preserved main_category > subcategory >
name.
So, considering I have Product and ProductCategory models defined I would
like to translate the mentioned query into Sequel DSL to then apply eager
loading with other associations I have defined in my Product model.
Is it possible?
Thanks!
--
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.
