Thanks Jeremy.
The delay almost works.
DenormalisedOrderLine3.dataset.clone(product_name: "Fish")
=> #<Sequel::SQLite::Dataset: "SELECT * FROM (SELECT `invoice_number`,
`product_name`, `customer_id` FROM `orders` LEFT OUTER JOIN (SELECT * FROM
`order_lines` WHERE (`product_name` IS NULL)) AS 't1' ON (`t1`.`order_id` =
`orders`.`id`)) AS 'orders'">
Unfortunately, the opts aren't picked up in the joined dataset. It works if
I delay/clone the joined dataset directly, but then I'm back to not being
able to create a class out of it.
DELAYED_ORDER_LINES = DATABASE[:order_lines].where(product_name:
Sequel.delay{|ds| ds.opts[:product_name]})
DELAYED_ORDER_LINES.clone(product_name: "Fish")
=> #<Sequel::SQLite::Dataset: "SELECT * FROM `order_lines` WHERE
(`product_name` = 'Fish')">
On Fri, Dec 20, 2019 at 2:00 PM Jeremy Evans <[email protected]> wrote:
> On Thursday, December 19, 2019 at 5:29:46 PM UTC-8, Beth Skurrie wrote:
>>
>> Hi,
>>
>> Thanks to everyone who works on Sequel or helps answer Sequel questions.
>>
>> I have a tricky problem that I'm trying to solve involving dynamic
>> queries, classes, eager loading, and performance. I've come up with a
>> simplified example to explain the problem, as the domain is quite complex.
>> My constraints are:
>>
>> * have a query which I think needs to be dynamically created, and
>> therefore, I believe can't be turned into a class along the lines of class
>> Foo < Sequel::Model(DB[:some_table].select(...)); end
>> * I want to eager load data on to the results of that query
>> * the dataset is very large so the query that I want to run takes a while
>>
>
>> Here is my contrived example.
>>
>> Imagine two tables, order and order_line.
>>
>> Order 100 has a fish and an umbrella. Order 2 has an Umbrella.
>>
>> Orders
>> -------------------
>> id | invoice_number
>> -------------------
>> 1 | 100
>> 2 | 200
>>
>> Order Lines
>> ----------------------------
>> id | product_name | order_id
>> ----------------------------
>> 1 | Umbrella | 1
>> 2 | Fish | 1
>> 3 | Umbrella | 2
>>
>>
>> A denormalised view of the orders and order lines looks like:
>>
>> -----------------------------
>> invoice_number | product_name
>> -----------------------------
>> 100 | Umbrella
>> 100 | Fish
>> 200 | Umbrella
>>
>> The SQL for this denormalised view is:
>>
>> SELECT invoice_number, product_name, customer_id FROM orders LEFT OUTER
>> JOIN order_lines ON (order_lines.order_id = orders.id)
>>
>> I want a report showing the presence, or lack of, a particular product in
>> an order. If an order does not have that product, I want to see a row with
>> a null in it. An example "fish" product report would look like:
>>
>> -----------------------------
>> invoice_number | product_name
>> -----------------------------
>> 100 | Fish
>> 200 | <null>
>>
>>
>> The SQL for the "presence of fish" report is:
>>
>> SELECT invoice_number, product_name, customer_id FROM orders LEFT OUTER
>> JOIN (SELECT * FROM order_lines WHERE (product_name = 'Fish')) AS
>> 'fish_order_lines' ON (fish_order_lines.order_id = orders.id)
>>
>> Note that the left outer join must be done on an *already filtered
>> dataset* to achieve the right results. If we try filtering the original
>> query, after joining order_line we lose the "200/null" row.
>>
>> I want to make a class to represent the product report order lines,
>> because I then want to do eager loading of a heap of other relationships on
>> each order line. This is how I'd do it for the full denormalised view,
>> without any filtering.
>>
>> class DenormalisedOrderLine <
>> Sequel::Model(DATABASE[:orders].select(:invoice_number, :product_name,
>> :customer_id).left_outer_join(:order_lines, { order_id: :id } ))
>> many_to_one :customer
>> end
>>
>> I can't do it this way for the product presence report however, because
>> the left outer joined dataset needs to have the dynamic product_name filter
>> applied to it *before* it's joined.
>>
>> I have tried dynamically creating a class on the fly to do this query:
>>
>> product_name = "..."
>> dynamic_class
>> =
>> Sequel::Model(DB[:orders].select(*COLUMNS).left_outer_join(DB[:order_lines].where(product_name:
>> product_name), { order_id: :id }))
>> dynamic_class.many_to_one :customer
>>
>> This works like a charm, however, when the model is defined, it runs the
>> query with a "limit 1" to get the definitions of the columns (I think). The
>> query that actually runs (remember, the code you're seeing here is
>> trivialised example) is quite an expensive query, and doing it twice makes
>> a significant impact on the response time.
>>
>
> You can probably use the columns_introspection plugin to avoid the second
> query:
> https://sequel.jeremyevans.net/rdoc-plugins/files/lib/sequel/extensions/columns_introspection_rb.html
>
> However, I think what you want is possible using a delayed evaluation
> (untested):
>
> class DenormalisedOrderLine <
> Sequel::Model(DATABASE[:orders].select(:invoice_number, :product_name,
> :customer_id).left_outer_join(DB[:order_lines].where(product_name:
> Sequel.delay{|ds| ds.opts[:product_name]}), { order_id: :id } ))
> many_to_one :customer
> end
>
> You can then do:
>
> DenormalisedOrderLine.dataset.clone(product_name: 'Fish').eager(:customer)
>
> I've tried messing around with `bind` but I can't see a way to turn that
>> into a class definition.
>>
>
> You can't use bind (the prepared statement support) in the definition of a
> model class dataset, because those are for specific queries, and a model
> class dataset needs to handle different types of queries.
>
> Thanks,
> Jeremy
>
> --
> You received this message because you are subscribed to a topic in the
> Google Groups "sequel-talk" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sequel-talk/QiQdNkw3Tv0/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sequel-talk/84ae963d-255b-46f5-9609-769a30036d2c%40googlegroups.com
> <https://groups.google.com/d/msgid/sequel-talk/84ae963d-255b-46f5-9609-769a30036d2c%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
>
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/CACO5b1Yan6rm7Mjwfpc7C1fg%3D8y3G5EKgLSQDoNsfX8uQd-dZA%40mail.gmail.com.
