Hi David, i did not see a problem with you current approach. It is simple you dislike literal sql?
You could write the subselect as a dbic statement and call as_query() and use it, see https://metacpan.org/pod/distribution/DBIx-Class/lib/DBIx/Class/Manual/Cookbook.pod#Correlated-subqueries my $inner = scm()->resultset('ServiceplanPrice')->search({ effective_date_time => { '<=' => $mocked_now }, type => 'new', serviceplan_id => $me.id, })->get_column('effective_date_time')->max_rs->as_query; ... "$foreign.effective_date_time" => {'=' => $inner}, ... Another idea would be to simple join without the effective_date_time (so you get 1..n) and erase the bad ones with WHERE and "DISTINCT ON" and the correct ORDER BY. Oh, this is perhaps only a PostgreSQL specific solution. ... DISTINCT ON (serviceplan.id) ... WHERE serviceplan_price.effective_date_time <= $mocked_now ... ORDER BY serviceplan_price.effective_date_time DESC ... Have a nice day Felix Ostmann Am Mo., 8. Okt. 2018 um 17:28 Uhr schrieb David Cantrell < david.cantr...@uk2group.com>: > Hello gang! > > I'm having trouble figuring out how to express one of my joins in > DBIx::Class. > > The two tables involved and some relevant sample data are: > > > select * from serviceplan_price; > +----+----------------+-------+-------+---------------------+ > | id | serviceplan_id | type | value | effective_date_time | > +----+----------------+-------+-------+---------------------+ > | 78 | 63 | new | 0.14 | 1973-01-01 00:00:00 | > | 79 | 64 | new | 0.73 | 1982-01-01 00:00:00 | > | 80 | 64 | new | 3.18 | 2012-01-01 00:00:00 | > | 81 | 63 | new | 2.99 | 2019-01-01 00:00:00 | > | 82 | 63 | renew | ... > > Note that the effective date can be in the future. This is how we > represent historical prices, and planned price rises. And that there are > two types, 'new' (the price you pay when you first buy something) and > 'renew' (the price for subsequent renewals). The 'new' price might > include the cost of setting up hardware, for example, which isn't needed > on renewal. > > > select * from serviceplan; > +----+--------------------------------+ > | id | name | irrelevant details ... | > +----+--------------------------------+ > | 63 | foo | blahblah | > | 64 | bar | blahblah | > +----+--------------------------------+ > > And I want to define a relationship so that, along with a serviceplan, I > can fetch its *current* new price or renewal price. For an added wrinkle > we want to be able to mock the current date/time in our tests, so we > can't just use NOW(), but I don't think that's the problem. In plain old > SQL it would look like this for fetching them with their current new price: > > SELECT me.id, me.name, ... > current_new_price.id, ... > FROM serviceplan me > JOIN serviceplan_price current_new_price ON ( > current_new_price.serviceplan_id = me.id AND > current_new_price.type = 'new' AND > current_new_price.effective_date_time = ( > SELECT MAX(effective_date_time) > FROM serviceplan_price > WHERE effective_date_time <= '$mocked' > AND type ='new' > AND serviceplan_id = me.id > ) > ) > > In terms of a DBIx::Class relationship on my serviceplan result class > I've got this (repeated for the current_renew_price): > > __PACKAGE__->belongs_to( > current_new_price => 'MyApp::Result::ServiceplanPrice', > sub ($args) { > my $foreign = $args->{foreign_alias}; > my $me = $args->{self_alias}; > my $mocked_now = MyApp::Mocks->now(), > return { > "$foreign.serviceplan_id" => { -ident => "$me.id" }, > "$foreign.type" => 'new', > "$foreign.effective_date_time" => { -ident => qq{ > ( SELECT MAX(effective_date_time) > FROM serviceplan_price > WHERE effective_date_time <= '$mocked_now' > AND type = 'new' > AND serviceplan_id = $me.id > ) > } } > } > } > ); > > I *think* that I have no choice but to write the relationship condition > as an anonymous sub, but embedding some raw SQL like that is just plain > hideous. Also I'd like to get rid of the repetition where I've said > twice that the 'type' field should be 'new' and that the serviceplan_id > should match, and the inconsistency where I refer to the foreign table > as $foreign is some places but by its true name inside the sub-select. > > Any clues on how to turn that into something a bit more SQL::Abstract? > > -- > David Cantrell > David Cantrell > System Architect > The Hut Group<http://www.thehutgroup.com/> > > Tel: > Email: david.cantr...@uk2group.com<mailto:david.cantr...@uk2group.com> > > For the purposes of this email, the "company" means The Hut Group Limited, > a company registered in England and Wales (company number 6539496) whose > registered office is at Fifth Floor, Voyager House, Chicago Avenue, > Manchester Airport, M90 3DQ and/or any of its respective subsidiaries. > > Confidentiality Notice > This e-mail is confidential and intended for the use of the named > recipient only. If you are not the intended recipient please notify us by > telephone immediately on +44(0)1606 811888 or return it to us by e-mail. > Please then delete it from your system and note that any use, > dissemination, forwarding, printing or copying is strictly prohibited. Any > views or opinions are solely those of the author and do not necessarily > represent those of the company. > > Encryptions and Viruses > Please note that this e-mail and any attachments have not been encrypted. > They may therefore be liable to be compromised. Please also note that it is > your responsibility to scan this e-mail and any attachments for viruses. We > do not, to the extent permitted by law, accept any liability (whether in > contract, negligence or otherwise) for any virus infection and/or external > compromise of security and/or confidentiality in relation to transmissions > sent by e-mail. > > Monitoring > Activity and use of the company's systems is monitored to secure its > effective use and operation and for other lawful business purposes. > Communications using these systems will also be monitored and may be > recorded to secure effective use and operation and for other lawful > business purposes. > > hgvyjuv > _______________________________________________ > List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class > IRC: irc.perl.org#dbix-class > SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ > Searchable Archive: > http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk >
_______________________________________________ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
