How do I do this search and order_by on a DBIx::Class::ResultSet

Question

Problem definition.

I have multiple clients with multiple users. Each client needs to be able to associate custom data with a user, search, and order by.

---

Database Solution:

A table Customfields which defines the customfields table. It has an id and name. It has a has_many relationship with a Userfields table (aka "attributes").

The Userfields table has a userid, customfieldid, content and id. It belongs_to a Useraccounts table (aka "useraccount") and Customfields (aka "customfield")

---

Proposed select statement that I want:

This is a select statement that achieves and produces what I need.

SELECT ua.*, (
    SELECT content FROM Userfields uf
    INNER JOIN Customfields cf
        ON cf.id = uf.customfieldid
    WHERE cf.name = 'Mothers birthdate'
    AND uf.uid=ua.uid
) AS 'Mothers birthdate',
    (
    SELECT content FROM Userfields uf
    INNER JOIN Customfields cf
        ON cf.id = uf.customfieldid
    WHERE cf.name = 'Join Date' AND
    uf.uid=ua.uid
) AS 'Join Date'
FROM UserAccounts ua
ORDER BY 'Mothers birthdate';

In this case their could be anything from 0 ... x sub select statements in the select statement and any one of them or none of them could be wanting to be ordered by.

---

Question

How do I achieve this with a ->search on my dbix class resultset or how do I achieve the same result with a search on my dbix class resultset?

Here is how I usually select from my Useraccounts table, although I am unsure how to do the complex statement that I want to from here.

my @users = $db->resultset('Useraccounts')->search(
    undef,
    {
        page        => $page,
        join        => 'attributes',
        ...
    });

Thanks for your time.

-pdh

Answer 1

This is really pretty hairy, and any solution isn't going to be pretty, but it does look to be possible if you bend the rules a little bit. Forgive any mistakes I make, as I didn't go and create a schema to test this on, but it's based on the best info I have (and much help from ribasushi).

First, (assuming that your userfields table has a belongs_to relation with the customfields table, called customfield)

my $mbd = $userfields_rs->search(
    {
      'customfield.name' => 'Mothers birthdate',
      'uf.uid' => \'me.uid' # reference to outer query
    },
    {
      join => 'customfield',
      alias => 'uf', # don't shadow the 'me' alias here.
    }
)->get_column('content')->as_query;

# Subqueries and -as don't currently mix, so hack the SQL ourselves
$mbd->[0] .= q{ AS 'Mothers Birthdate'};

The literal me.uid that uf.uid is being matched against is an unbound variable -- it's the uid field from the query that we're eventually going to put this query into as a subselect. By default DBIC aliases the table that the query is addressing to me; if you gave it a different alias then you would use something diferent here. Anyway, You could repeat this as_query business with as many different fields as you like, just varying the field-name (if you're smart, you'll write a method to generate them), and put them in an array, so now let's suppose that @field_queries is an array, containing $mbd above as well as another one based on Join Date, and anything you like.

Once you have that, it's as "simple" as...

my $users = $useraccounts_rs->search(
    { }, # any search criteria can go in here,
    {
      '+select' => [ @field_queries ],
      '+as' => [qw/mothers_birthdate join_date/], # this is not SQL AS
      order_by => {asc => 'Mothers birthdate'},
    }
);

which will include each of the subqueries into the select.

Now for the sad part: as of right now, this whole thing actually won't work, because subqueries with placeholders don't work properly. So for now you need an additional workaround: instead of 'customfield.name' => 'Mothers birthdate' in the subselect search, do 'customfield.name' => \q{'Mothers birthdate'} -- this is using literal SQL for the field name (BE CAREFUL of SQL injection here!), which will sidestep the placeholder bug. But in the not-too-distant future, that bug will be resolved and the code above will work okay, and we'll update the answer to let you know that's the case.