[Dbix-class] Joins using sub-selects

Tue Oct 9 06:48:33 GMT 2018

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.cantrell at 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.cantrell at uk2group.com<mailto:david.cantrell at 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20181009/8d322f18/attachment-0001.htm>