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/0e4fc643-231f-4c9a-97fc-37f4923c319a%40googlegroups.com.
