Btw, I have created examples of each of my trial methods here:
https://gist.github.com/bethesque/26734311f7ec95072c91e67eda831fca
On Friday, 20 December 2019 12:29:46 UTC+11, 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.
>
> I've tried messing around with `bind` but I can't see a way to turn that
> into a class definition.
>
> To summarise, I'd like a way to:
>
> * Have a dynamic dataset *or* a way to create a class where it won't run
> the query on definition *or *a way to define a class where the variables
> can be bound at runtime
> * Eager load data on to the results
>
> Is this possible, or am I asking too much of an already incredibly
> flexible and amazingly featured library?
>
> If you go this far, thank you for reading!
>
> Beth
>
>
--
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/7280738b-0d9b-4d5d-a0b7-51e0d2ebed21%40googlegroups.com.
