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 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/84ae963d-255b-46f5-9609-769a30036d2c%40googlegroups.com.
