Hello Mr. Evans! Thank you for your Feedback! >> Most SQL can be put into Sequel's DSL. You might need to use literal strings for part of it, but the only time you really cannot use Sequel's DSL if you are using database-specific clauses that Sequel doesn't support. If you post the parts of the SQL you are using that you don't know how to translate to Sequel's DSL, I could probably help with that.
Ok, yes I agree with you, but in my case it's a sql which is managed by BI-men and the query is adapted all the time. I don't want to translate their sql's all the time to great Sequel-DSL. >> It is almost always going to be a bad idea to provide custom SQL for a dataset when creating the model. That would mean all access through the model would use the same SQL, regardless of what operation you were performing. In my case this is no big issue, because there will be only one access to the Model-Module (get bookings and their services paged). But I will go with the manual plain-hash-version, because I suspect that the performance is even a bit better, than the model approach (Please correct me if I'm wrong). Performance is very important for me. I have to load ten-thousands of bookings with their services. >> A couple problems here. First, calling to_a on a eager dataset does not do eager loading, you have to call all (or use the eager_each extension). Second, I couldn't reproduce the error you were getting. Can you create a self contained example showing the problem Thank you for the hints. Here's the self contained example, where I observe the error: class Service < Sequel::Model(DAVINCI_DB["select 2 \"id\", '1' \"booking_id \", 'Service Hotel' \"name\""]) end class Booking < Sequel::Model(DAVINCI_DB["select 1 \"id\", 'Booking Package Mallorca' \"name\""]) one_to_many :services, class: Service, key: :booking_id end puts Booking.where(id: 1).all # this works, but only with implicit_subquery enabled puts Booking.eager(:services).all # this fails with Sequel::Error: No source specified for query >> If this is the only reason you are using models, I would probably do it manually. You may find to_hash_groups useful for part 2. Thank you for the useful to_hash_groups-method. I will definitely use it to load the services, because then the allocation to the bookings is easier. I'm just a little concerned about the extra iteration over the bookings during the distribution, but the eager attempt will have that too, right? Thank you Mr. Evans! Best regards -- Armin Jeremy Evans schrieb am Mittwoch, 6. Oktober 2021 um 16:18:03 UTC+2: > On Wed, Oct 6, 2021 at 6:57 AM armin <ar...@wurzweb.com> wrote: > >> Hello Mr. Evans, >> >> I want to create 2 model-classes. A booking model which has a >> one_to_many-connection to a service model. I want to load some bookings and >> eager load the services. >> >> The difficulty is that the source of the two models relates to complex >> sqls, which I cannot change or put it into sequel-dsl. I tried following: >> > > Most SQL can be put into Sequel's DSL. You might need to use literal > strings for part of it, but the only time you really cannot use Sequel's > DSL if you are using database-specific clauses that Sequel doesn't > support. If you post the parts of the SQL you are using that you don't > know how to translate to Sequel's DSL, I could probably help with that. > > >> class Booking < Sequel::Model(DB["select complex.id id, ...."]) >> one_to_many :services, key: :booking_id >> end >> >> class Service < Sequel::Model(DB["select complex.id id, >> complex.booking_id...."]) >> one_to_many :services, key: :booking_id >> end >> > > It is almost always going to be a bad idea to provide custom SQL for a > dataset when creating the model. That would mean all access through the > model would use the same SQL, regardless of what operation you were > performing. > > >> Now I want to eager load the bookings: >> Booking.eager(:services).limit(5).to_a, but get a >> Sequel::Error: >> No source specified for query >> if I try to access booking.services >> > > A couple problems here. First, calling to_a on a eager dataset does not > do eager loading, you have to call all (or use the eager_each extension). > Second, I couldn't reproduce the error you were getting. Can you create a > self contained example showing the problem > > >> Basic filtering is also ignored. Following returns bookings, but >> unfiltered. >> >> Booking.where(id: 1).to_a >> > > Expected, as you are providing custom SQL when creating the model. You > could try using the implicit_subquery extension to work around that, but a > better fix is to not use custom SQL when creating the model. > > >> and following returns just the booking with the given id. >> >> Booking.limit(10000).where(id: 1).to_a >> >> The limit-method-call makes it work. I do not know why. >> > > The limit method will always use a subquery when the receiver has custom > SQL, even without the use of the implicit_subquery extension. > > >> How I can achieve my goal? I'm just using models, because I want to eager >> load the services. The result should be bookings holding their services. >> Whether >> it is a hash or a model object is not so important. >> Or do I have to do the association-loading manually in this case: >> 1. Load a punch of services >> 2. Load the services of 1 and distribute the services to the >> corresponding bookings >> > > If this is the only reason you are using models, I would probably do it > manually. You may find to_hash_groups useful for part 2. > > 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 sequel-talk+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sequel-talk/42c641bf-4837-4ccf-b3b8-3898e760ffc0n%40googlegroups.com.
