On Wednesday, April 5, 2017 at 1:28:17 PM UTC-7, Xander Flood wrote: > > I have a piece of unexpected behavior which I believe is a bug, but it > really depends on how one describes what Sequel does. If methods like > `select`, `where`, and `join` are to be seen as adding clauses to a query, > there is an interpretation in which this behavior is not a bug. However, if > we consider them as performing lazy-loaded operations on a database (and > therefore stipulate that the result a chain of such method calls should be > interpreted in order they are made) then this is most definitely a bug. > Tell me what you think. >
I think it would help if you actually provided some small example code for what you are doing. However, what you are describing doesn't sound like a bug to me. In general, Sequel can handle dataset method calls in any order (e.g. where.select.order compared to order.where.select), but there are definitely cases where the order matters (e.g. order.unordered compared to unordered.order). > Sequel reorders clauses in a way that breaks if the same table is joined > repeatedly. It seems that, regardless of the order of method calls, Sequel > will float all JOIN clauses into a bloc together and WHERE into another > bloc that comes after the JOINS. This involves interchanging the order of > WHEREs and JOINs, which is normally this is OK, but in my use case, it is > not. I have code that does the following: > > 1. Start with table_1. > 2. Join with table_2, and do a WHERE according to the columns from table_2. > 3. Select only the columns from table_1. > 4. Join with table_2, and do a WHERE according to the columns from table_2. > 3. Select only the columns from table_1. > > The reason we can't get away with a single join is that this is part of a > search library that filters table_1 by repeatedly joining it with other > tables. The block that performs the second join doesn't know whether this > particular table has been joined in before, and treats the `dataset` it > receives as though it were just an unfiltered table. > The block that performs the second join can easily look at dataset.opts[:joins] and introspect what tables have already been joined, and use that information to decide whether to join to another table. > However, the actual behavior of the Sequel gem is doesn't allow that kind > of modularity. While the query is being built, Sequel rearranges these > steps into: > > 1. Start with table_1. > 2. Join with table_2. > 2. Join with table_2 again. > 3. Attempt to apply the combined WHERE clause, but fail because of > duplicate columns. > > Each time I call the inner_join method, there are no column naming > conflicts between the `dataset` being operated on and the table being > join'd in. However, the joins themselves occur in a different order, and > this is what causes the error. If the `join` method means "add a JOIN > clause to the bloc of JOIN clauses located at the beginning of this query," > then this is the intended behavior, and my usage is wrong. If the `join` > method means "join the result of the previous query in the following way," > then this behavior is incorrect and the reordering of clauses is not > acceptable. The description in the docs says "Returns a joined dataset.", > which is somewhat ambiguous on this point, but seems to point towards the > latter conclusion. > If you are joining to the same table multiple times (and that is what you want in SQL), you should be explicitly specifying the alias for each join, and manually keeping track of the aliases. Automatically aliasing tables for joins can hide bugs, so Sequel only does it in certain cases (eager_graph is one). > A workaround for anyone else having this issue is to use > `unused_table_alias` before each join to get a safe table alias. In my > opinion, the Sequel gem should alias these tables behind the scenes, > automatically, when a situation like this arises, or else it should alias > the entire expression resulting from the first JOIN/WHERE/SELECT bloc, and > then use that as the target of the next JOIN/WHERE/SELECT. > If you want to wrap the join in a subquery, you can use join(...).from_self(:alias=>:foo). I'm not exactly sure what behavior you want, but I'm guessing the changes to implement it would not be backwards compatible. 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 https://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/d/optout.
