<div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small">Hi David,</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small"><br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small">i did not see a problem with you current approach. It is simple you dislike literal sql?</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small"><br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small">You could write the subselect as a dbic statement and call as_query() and use it, see</div><div class="gmail_default"><font face="arial, helvetica, sans-serif"><a href="https://metacpan.org/pod/distribution/DBIx-Class/lib/DBIx/Class/Manual/Cookbook.pod#Correlated-subqueries">https://metacpan.org/pod/distribution/DBIx-Class/lib/DBIx/Class/Manual/Cookbook.pod#Correlated-subqueries</a></font><br></div><div class="gmail_default"><font face="arial, helvetica, sans-serif"><br></font></div><div class="gmail_default"><font face="arial, helvetica, sans-serif">my $inner = scm()->resultset('</font>ServiceplanPrice<span style="font-family:arial,helvetica,sans-serif">')->search({</span></div><div class="gmail_default">effective_date_time => { '<=' => $mocked_now },</div>type =<span class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small">></span> 'new'<span class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small">,</span><br>serviceplan_id =<span class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small">></span> $<a href="http://me.id/" rel="noreferrer" target="_blank">me.id</a><span class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small">,</span></div><div dir="ltr"><font face="arial, helvetica, sans-serif"><span class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small">})-></span></font><code class="gmail-perl gmail-plain" style="font-size:12px;background:none rgb(245,245,245);white-space:pre;box-sizing:content-box;font-family:inherit;padding:0px;color:black;border-radius:0px;border:0px;float:none;height:auto;line-height:normal;margin:0px;outline:0px;overflow:visible;vertical-align:baseline;width:auto;min-height:auto">get_column(</code><code class="gmail-perl gmail-string" style="font-size:12px;background:none rgb(245,245,245);white-space:pre;box-sizing:content-box;font-family:inherit;padding:0px;color:blue;border-radius:0px;border:0px;float:none;height:auto;line-height:normal;margin:0px;outline:0px;overflow:visible;vertical-align:baseline;width:auto;min-height:auto">'<span class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small"></span><span style="color:rgb(34,34,34);font-size:small;white-space:normal;background-color:rgb(255,255,255)"><span class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small"></span><span class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small"></span>effective_date_time</span>'</code><code class="gmail-perl gmail-plain" style="font-size:12px;background:none rgb(245,245,245);white-space:pre;box-sizing:content-box;font-family:inherit;padding:0px;color:black;border-radius:0px;border:0px;float:none;height:auto;line-height:normal;margin:0px;outline:0px;overflow:visible;vertical-align:baseline;width:auto;min-height:auto">)->max_rs->as_query</code><font face="arial, helvetica, sans-serif"><span class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small">;</span></font></div><div dir="ltr"><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small">...</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small"><span style="font-family:Arial,Helvetica,sans-serif">"$foreign.effective_date_</span><span style="font-family:Arial,Helvetica,sans-serif">time" => {'=' => $inner},</span></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small">...</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small"><br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small"><br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small">Another idea would be to simple join without the <span class="gmail_default"></span><span style="font-family:Arial,Helvetica,sans-serif">effective_date_time (so you get 1..n) and erase the bad ones with WHERE and</span><span style="font-family:Arial,Helvetica,sans-serif"> </span><span style="font-family:Arial,Helvetica,sans-serif">"DISTINCT ON" and the correct ORDER BY. Oh, this is perhaps only a PostgreSQL specific solution.</span></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small"><span style="font-family:Arial,Helvetica,sans-serif"><br></span></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small"><span style="font-family:Arial,Helvetica,sans-serif">...</span></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small"><span style="font-family:Arial,Helvetica,sans-serif">DISTINCT ON (</span><span style="font-family:Arial,Helvetica,sans-serif"><a href="http://serviceplan.id">serviceplan.id</a>)</span></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small"><span style="font-family:Arial,Helvetica,sans-serif">...</span></div><div class="gmail_default"><span class="gmail_default" style="font-size:small">WHERE </span>serviceplan_price.<span class="gmail_default" style="font-family:arial,helvetica,sans-serif"></span>effective_date_time <= $mocked_now</div><div class="gmail_default">...</div><div class="gmail_default">ORDER BY serviceplan_price.effective_date_time DESC</div><div class="gmail_default">...</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small"><span style="font-family:Arial,Helvetica,sans-serif"><br></span></div></div><div dir="ltr"><div><div dir="ltr" class="gmail_signature"><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small">Have a nice day</div>Felix Ostmann</div></div></div></div></div></div></div></div></div></div></div></div></div></div></div><br><div class="gmail_quote"><div dir="ltr">Am Mo., 8. Okt. 2018 um 17:28 Uhr schrieb David Cantrell <<a href="mailto:david.cantrell@uk2group.com">david.cantrell@uk2group.com</a>>:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hello gang!<br>
<br>
I'm having trouble figuring out how to express one of my joins in<br>
DBIx::Class.<br>
<br>
The two tables involved and some relevant sample data are:<br>
<br>
> select * from serviceplan_price;<br>
+----+----------------+-------+-------+---------------------+<br>
| id | serviceplan_id | type | value | effective_date_time |<br>
+----+----------------+-------+-------+---------------------+<br>
| 78 | 63 | new | 0.14 | 1973-01-01 00:00:00 |<br>
| 79 | 64 | new | 0.73 | 1982-01-01 00:00:00 |<br>
| 80 | 64 | new | 3.18 | 2012-01-01 00:00:00 |<br>
| 81 | 63 | new | 2.99 | 2019-01-01 00:00:00 |<br>
| 82 | 63 | renew | ...<br>
<br>
Note that the effective date can be in the future. This is how we<br>
represent historical prices, and planned price rises. And that there are<br>
two types, 'new' (the price you pay when you first buy something) and<br>
'renew' (the price for subsequent renewals). The 'new' price might<br>
include the cost of setting up hardware, for example, which isn't needed<br>
on renewal.<br>
<br>
> select * from serviceplan;<br>
+----+--------------------------------+<br>
| id | name | irrelevant details ... |<br>
+----+--------------------------------+<br>
| 63 | foo | blahblah |<br>
| 64 | bar | blahblah |<br>
+----+--------------------------------+<br>
<br>
And I want to define a relationship so that, along with a serviceplan, I<br>
can fetch its *current* new price or renewal price. For an added wrinkle<br>
we want to be able to mock the current date/time in our tests, so we<br>
can't just use NOW(), but I don't think that's the problem. In plain old<br>
SQL it would look like this for fetching them with their current new price:<br>
<br>
SELECT <a href="http://me.id" rel="noreferrer" target="_blank">me.id</a>, <a href="http://me.name" rel="noreferrer" target="_blank">me.name</a>, ...<br>
<a href="http://current_new_price.id" rel="noreferrer" target="_blank">current_new_price.id</a>, ...<br>
FROM serviceplan me<br>
JOIN serviceplan_price current_new_price ON (<br>
current_new_price.serviceplan_id = <a href="http://me.id" rel="noreferrer" target="_blank">me.id</a> AND<br>
current_new_price.type = 'new' AND<br>
current_new_price.effective_date_time = (<br>
SELECT MAX(effective_date_time)<br>
FROM serviceplan_price<br>
WHERE effective_date_time <= '$mocked'<br>
AND type ='new'<br>
AND serviceplan_id = <a href="http://me.id" rel="noreferrer" target="_blank">me.id</a><br>
)<br>
)<br>
<br>
In terms of a DBIx::Class relationship on my serviceplan result class<br>
I've got this (repeated for the current_renew_price):<br>
<br>
__PACKAGE__->belongs_to(<br>
current_new_price => 'MyApp::Result::ServiceplanPrice',<br>
sub ($args) {<br>
my $foreign = $args->{foreign_alias};<br>
my $me = $args->{self_alias};<br>
my $mocked_now = MyApp::Mocks->now(),<br>
return {<br>
"$foreign.serviceplan_id" => { -ident => "$<a href="http://me.id" rel="noreferrer" target="_blank">me.id</a>" },<br>
"$foreign.type" => 'new',<br>
"$foreign.effective_date_time" => { -ident => qq{<br>
( SELECT MAX(effective_date_time)<br>
FROM serviceplan_price<br>
WHERE effective_date_time <= '$mocked_now'<br>
AND type = 'new'<br>
AND serviceplan_id = $<a href="http://me.id" rel="noreferrer" target="_blank">me.id</a><br>
)<br>
} }<br>
}<br>
}<br>
);<br>
<br>
I *think* that I have no choice but to write the relationship condition<br>
as an anonymous sub, but embedding some raw SQL like that is just plain<br>
hideous. Also I'd like to get rid of the repetition where I've said<br>
twice that the 'type' field should be 'new' and that the serviceplan_id<br>
should match, and the inconsistency where I refer to the foreign table<br>
as $foreign is some places but by its true name inside the sub-select.<br>
<br>
Any clues on how to turn that into something a bit more SQL::Abstract?<br>
<br>
--<br>
David Cantrell<br>
David Cantrell<br>
System Architect<br>
The Hut Group<<a href="http://www.thehutgroup.com/" rel="noreferrer" target="_blank">http://www.thehutgroup.com/</a>><br>
<br>
Tel:<br>
Email: <a href="mailto:david.cantrell@uk2group.com" target="_blank">david.cantrell@uk2group.com</a><mailto:<a href="mailto:david.cantrell@uk2group.com" target="_blank">david.cantrell@uk2group.com</a>><br>
<br>
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.<br>
<br>
Confidentiality Notice<br>
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.<br>
<br>
Encryptions and Viruses<br>
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.<br>
<br>
Monitoring<br>
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.<br>
<br>
hgvyjuv<br>
_______________________________________________<br>
List: <a href="http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class" rel="noreferrer" target="_blank">http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class</a><br>
IRC: <a href="http://irc.perl.org#dbix-class" rel="noreferrer" target="_blank">irc.perl.org#dbix-class</a><br>
SVN: <a href="http://dev.catalyst.perl.org/repos/bast/DBIx-Class/" rel="noreferrer" target="_blank">http://dev.catalyst.perl.org/repos/bast/DBIx-Class/</a><br>
Searchable Archive: <a href="http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk" rel="noreferrer" target="_blank">http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk</a><br>
</blockquote></div>