How to Subselect with DBIx::Class?

Question

I'm starting with DBIx::Class and i have a subselect that wanted to be in DBIx::Class, but i'm getting confused and can't build the code.

My MySQL select is this one:

Select name from tblCategory where id = (
    Select id from tblCategory where id = (
         Select id from tblRadio where name = "RFM"
    )
);

I read that DBIx::Class don't support subselect; is that true? If so, what do you do in situations like this?

Answer 1

Well, you can always supply a scalar reference to insert literal SQL when you're using DBIC's search() method. For example:

my $rs = $schema->resultset('Category')->search({ 
              id => \"(Select id from tblRadio where name = 'RFM')" 
});

That's what I've had to do in the past when I needed more expressiveness than DBIC supported out-of-the-box. I don't know, though, whether that's the "right" thing to do in this case.

Answer 2

Couldn't this be represented as a join?

my $rs = $schema->resultset('Category')->search(
    {   
       'Radio.name' => 'RFM' 
    },
    {   
        'join' => 'Radio'
    }   
);

This assumes you have a relationship in Category named 'Radio'. If you don't, there's plenty of documentation to help you setup relationships and learn how to perform joins.

As for subqueries, the cookbook for the most recent version says they are supported, but experimental.

Answer 3

According to the DBIx::Class::Manual::Cookbook there is a new Subquery feature:

my $inside_rs = $schema->resultset('Radio')->search({ name => 'RFM' });

my $rs = $schema->resultset('Category')->search({
    id => { '=' => $inside_rs->get_column('id')->as_query },
});

It is marked EXPERIMENTAL so YMMV.

However also note that SQL::Abstract which DBIx::Class uses when building its queries does have a new subquery feature using -nest.

Answer 4

Hi again, after some fight with DBIC i win in the end :P (YEAH!)

Had to rewrite some stuff, and had to forget the subselects and done the relationships well.

I know that this code don't represent the inicial question, but if i had to rewrite all again, i'm showing you the other part of my "project", where i had again other dificulties.

Next is what i've done:

DBIx::Class Schema

package DB::Esquema::Passwords;

use strict;
use warnings;

use base 'DBIx::Class';

__PACKAGE__->load_components("Core");
__PACKAGE__->table("Passwords");
__PACKAGE__->add_columns(
  "pswd",
  { data_type => "INT", default_value => undef, is_nullable => 0, size => 11 },
  "password",
  {
    data_type => "VARCHAR",
    default_value => undef,
    is_nullable => 1,
    size => 20,
  },
  "utilizadorid",
  { data_type => "INT", default_value => undef, is_nullable => 1, size => 11 },
);
__PACKAGE__->set_primary_key("pswd");
__PACKAGE__->belongs_to('utilizadorid' => 'DB::Esquema::Utilizadores');
#belongs_to is not autogenerated, done by hand

Has a relationship with Utilizadores (users)

package DB::Esquema::Utilizadores;

use strict;
use warnings;

use base 'DBIx::Class';

__PACKAGE__->load_components("Core");
__PACKAGE__->table("Utilizadores");
__PACKAGE__->add_columns(
  "utilizador",
  { data_type => "INT", default_value => undef, is_nullable => 0, size => 11 },
  "nome",
  {
    data_type => "VARCHAR",
    default_value => undef,
    is_nullable => 1,
    size => 20,
  },
  "mail",
  {
    data_type => "VARCHAR",
    default_value => undef,
    is_nullable => 1,
    size => 30,
  },
);
__PACKAGE__->set_primary_key("utilizador");
__PACKAGE__->has_one('utilizador' => 'DB::Esquema::Passwords', 'utilizadorid');

NEXT (the script to make it work)

#!/usr/bin/perl -w

use strict;
use diagnostics; #was important to understand
use lib '/var/www/projectox/lib'; #is where the schema is
use DB::Esquema; #use the Schema

system('clear'); # clear the screen

my $esquema = DB::Esquema->connect("dbi:mysql:dbname=dbswiak","root","");
    $esquema->storage->debug(1);

    #HAD TO USE PREFETCH
    my $resultado = $esquema->resultset('Utilizadores')->search(
    undef,{
         prefetch => { 'utilizador' => 'utilizadorid' }
      }
    )->next();

The Result:

    SELECT me.utilizador, me.nome, me.mail, utilizador.pswd, 
utilizador.password, utilizador.utilizadorid, utilizadorid.utilizador, utilizadorid.nome, utilizadorid.mail 
FROM Utilizadores me JOIN Passwords utilizador 
ON utilizador.utilizadorid = me.utilizador 
JOIN Utilizadores utilizadorid ON utilizadorid.utilizador = utilizador.utilizadorid:

Wasn't what i really wanted, but is the nearest, the goal is to select only the columns i want... maybe i will reach that goal

Answer 5

Subselects are experimental feature now and useful if you need to be able to allow them at compile time. However I find the following to be a good approach.

(1) most subselects can be done as a (faster) join - so use a join where you can (2) failing that, create a view in the database and a schema class for the view.