On Monday, February 11, 2019 at 12:40:35 PM UTC-8, Matthew Schoolmaster wrote: > > Hello- > > I have a straight forward many_to_one relationship and am using the > rcte_tree plugin as follows: > > class Item < Sequel::Model(:items) > many_to_one :category > end > > class Category < Sequel::Model(:categories) > plugin :rcte_tree > one_to_many :items > end > > This is working quite well, though I find myself wanting to preform > queries like: > > Item.where( category: Category[10].descendants_dataset.select(:id) ) > > But this results in an error having to do with defining the CTE within the > where clause. (Sequel::DatabaseError: TinyTds::Error: Incorrect syntax near > the keyword 'WITH'.) I realize that I can do something like the following: > > Item.where( category: Category[10].descendants_dataset.select_map(:id) ) > > But in my application we have particularly deep descendant trees and > performance is an issue. I'm using TinyTDS and SQL Server 2008 R2. > > It appears the solution is to specify the CTE prior to the where clause > prefixed with a semicolon, but have been unable to sort out if I can > accomplish this with Sequel. Do you have any recommendations? I saw a > previous reply to a similar setup in 2015 ( > https://groups.google.com/forum/#!searchin/sequel-talk/cte|sort:date/sequel-talk/xYf6erRSAi8/exvgXkW6AAAJ) > > but this approach also fails here. >
I believe SQL server does not support CTEs in subqueries, you would have to move such CTEs to the main query: ds = Category[10].descendants_dataset.select(:id) Item.where(:category=>ds.clone(:with=>nil)).clone(:with=>ds.opts[:with]) Sequel has code to automatically hoist CTEs out of subqueries in Dataset#from, #join_table, and #with, but not in #where as it would require processing the entire expression tree for every call to #where (seriously hurting performance). 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.
