On Thursday, November 5, 2015 at 5:25:24 PM UTC-3, Jeremy Evans wrote:
>
> 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
>
Thanks for your reply Jeremy. Yes I am using Sequel models. In your query,
when you qualify the products table, instead of :product shouldnt it be
:producs?
Unfortunately your aproarch did not work. I mean, it worked, but not with
the expected order. I think the main problem there is with null
"main_category" values. And those are the products that do not belong to
any subcategories. Or is the same to say: products that belong to
"main_categories". So for example, with a local test I obtained the
following result executing the query:
name | id | product_category_id |
main_category | subcategory
----------------------------------------+----+---------------------+---------------+--------------------
Agua Mineral 200 cm3 edit2 | 9 | 11 | Bebidas
| Sin alcohol
Agua Mineral Gasificada | 50 | 11 | Bebidas
| Sin alcohol
Coca-Cola 375 cm3 | 1 | 11 | Bebidas
| Sin alcohol
Tinto | 21 | 9 | Bebidas
| Vinos
Medialunas | 8 | 30 | Cafeter
ía | Medialunas
Muzzarella a la Milanesa | 44 | 20 |
Entradas | Calientes
Revuelto de Gramajo | 47 | 20 |
Entradas | Calientes
Tortilla de Papas | 52 | 20 |
Entradas | Calientes
Salpicon de Ave | 49 | 19 |
Entradas | Frias
Vitel Tone | 45 | 19 |
Entradas | Frias
Aquarius | 36 | 1 |
| Bebidas
Coca Zero | 54 | 1 |
| Bebidas
Crush | 34 | 1 |
| Bebidas
Paso de los Toros | 35 | 1 |
| Bebidas
Pesi | 46 | 1 |
| Bebidas
Sprite 375 cm3 | 23 | 1 |
| Bebidas
Cafe Chico | 6 | 2 |
| Cafetería
Cafe Jarrito | 7 | 2 |
| Cafetería
Capuccino | 18 | 2 |
| Cafetería
Submarino | 24 | 2 |
| Cafetería
Picada Chica | 53 | 13 |
| Entradas
<> años ü árbol | 14 | 3 |
| Platos Principales
Berenjenas en Escabeche | 19 | 3 |
| Platos Principales
Bife de Kobe | 22 | 3 |
| Platos Principales
Milanesa Napolitana | 11 | 3 |
| Platos Principales
Papas Fritas | 12 | 3 |
| Platos Principales
Pizza elab | 17 | 3 |
| Platos Principales
Pizza Muzarella | 2 | 3 |
| Platos Principales
Producto largo y Probl3m@tica. <>ü; <> | 13 | 3 |
| Platos Principales
bombon suiz | 15 | 4 |
| Postres
Brownie | 27 | 4 |
| Postres
Cheessecake | 26 | 4 |
| Postres
Ensalada de Frutas | 10 | 4 |
| Postres
Panqueue Dulce de Leche | 20 | 4 |
| Postres
As you can see, the order is partially correct. The columns also are not
representing the correct category level, for example Aquarius belong to
main category "Bebidas", but has none subcategory.
I was able to migrate the query to DSL. I am not happy with the result, but
it worked. Is there any improvement I can make?
subcategories_dataset = ProductCategory
.left_join(:product_categories___pcc, id: :parent_id)
.select(:product_categories__id,
Sequel.function(:coalesce, :pcc__name, :product_categories__name).
as(:main_category),
Sequel.case([[Sequel.lit('pcc.name is not null'), :
product_categories__name]], nil).as(:subcategory))
Product
.exclude(product_type_id: 3)
.inner_join(subcategories_dataset, id: :product_category_id)
.select_all(:products)
.select_append(:t1__main_category, :t1__subcategory)
.eager({ proportions: :ingredient }, { product_category: :parent }, :
provider)
.order(:t1__main_category, Sequel.asc(:t1__subcategory, nulls: :first), :
products__name)
I am eagerly loading other models I need, but the really important thing I
need, is to return the products in the corrrect order, then is trivial.
Thanks a lot for your support 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.
