On Thursday, November 5, 2015 at 1:45:26 PM UTC-8, flyer wrote:
>
>
>
> 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
>
Yeah, the results are different in those cases. If you want the results
you originally posted, you will have to use a similar case statement as you
used in your subquery approach for both the main_category and subcategory.
Something like:
Product.association_left_join(:category=>:parent).
exclude(:category__id=>nil).
select(:product__name, :product__id, :product__product_category_id,
Sequel.function(:coalesce, :parent__name,
:category__name).as(:main_category),
Sequel.case({{:parent__name=>nil}=>nil},
:category__name).as(:subcategory)).
order(4,5,1)
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.
