How do I use a sub query in the SELECT part with DBIx::Class?

Question

Please help to write this MySQL query in DBIx::Class (to have an example on how to use subqueries in DBIx::Class):

SELECT x, (SELECT COUNT(*) FROM t2 WHERE t2.y=x) AS c FROM t1 WHERE t1.z=123

(I know that it can be rewritten as JOIN with GROUP BY, but I want namely subquery (for an example to get my hands on).)

DBIx::Class docs give extensive review of using subqueries in WHERE clause but I didn't find how to use a subquery in the SELECT fields list (as in the above example).

Answer 1

You were on the right track. The as_query is what you need. But you also need the columns option in the second hashref. Make sure you use count_rs on the sub query, which flips the internal switch to produce a resultset with COUNT(*) in it.

my $obj = ResultSet('TableOne')->search(
    { 
         z => 123,
    },
    {
        columns => [
            'x',
            {
                c => ResultSet('TableTwo')->search(
                    {
                        'y' => {
                            -ident => 'me.x',
                        },
                    },
                    {
                        alias => 'sub_query', # this name is arbitrary
                    },
                )->count_rs->as_query,
            },
        ],
    }
)->first;

The resulting query will look something like this:

SELECT me.x, ( 
    SELECT COUNT( * ) 
      FROM table_two sub_query
    WHERE y = me.x
   )
  FROM table_one me
WHERE me.z = 123

As you can see, the value that we selected via the sub query is not called c in the SQL, but it is called c in the object's data.

use DDP;
p $obj;

DB::Result::TableOne  {
    # ...
    internals: {
        _column_data     {
            x        "foo",
            c        "bar"
        },
        _in_storage      1,
        _result_source   DBIx::Class::ResultSource::Table
    }
}

You can access it with get_column on the row object.

say $obj->get_column('c');
# bar