How to choose DBIx :: Class?

Question

I start with DBIx :: Class and I have a subnet that wanted to be in DBIx :: Class, but I'm confused and cannot create the code.

My choice of MySQL is:

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

I read that DBIx :: Class does not support a subquery; it's true? If so, what do you do in such situations?

Answer 1

According to there is a new subquery function: DBIx::Class::Manual::Cookbook

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 labeled EXPERIMENTALLY, so YMMV.

However, also note that the one that it uses when building its queries has a new subquery function that uses -nest. SQL::Abstract DBIx::Class

Answer 2

Could this be represented as a mix?

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

, "". , , .

, , , experimental.

Answer 3

, SQL, DBIC search(). :

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

, , , DBIC, . , , "" .

Answer 4

DBIC : P (YEAH!)

.

, -, , "", .

:

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

Utilizadores ()

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');

(script, )

#!/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();

:

    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: 

, , , , , ... ,

Answer 5

, . , :

1. ( ) , ,
2. otherwise, create a view in the database and a schema class for the view.