Get JOIN query from CDbCriteria.with

Yii 1.1 General Discussion
1

I can’t manage to find a way to build a complete query from a CDbCriteria.

The CDbCommandBuilder has a method that creates the select query from a CDbCriteria, but ignores the "with" part.

What i need to to, is run a "CREATE TABLE … AS SELECT …" starting from the CDbCriteria associated with a CGridView. I have the criteria, all i need is the SELECT statement.

Does anybody have any ideeas ?

I looked around a lot and managed to do something myself, but i think it’s kind of clumbsy, and would require access to the “_joinTree” attribute of CActiveFinder, and that’s declared as PRIVATE.

2

try CDbCommand class like:




            $sql = "CREATE TABLE tbl_xxxx.... AS SELECT ....";

            $command = Yii::app()->db->createCommand($sql);

            $command->bindValue(':tbl_xxxx', 'xxxx', PDO::PARAM_STRING);

            return $command->execute();
3

That’s not really what I wanted.

I want/need to recreate the JOIN part of a query, starting from a CDbCriteria object with the "with" property set to an array of tables.

Does anybody know how or if this is possible ?

Thank you!

4

Can’t no body help ?

5

Is not possible. CDbCriteria is usefull only for select, if you want to change the structure of the database you should run a query with cdbcommand

6

Ok, forget the CREATE part, just tell me how to get the full SELECT starting from a CDBCriteria that has the “with” attribute set to something! :)

7

I got the same problem, im converting one CDbCriteria to CDbCommand, but the JOIN part is left behind when I use CDbCommandBuilder->createFindCommand() passing my criteria.

My criteria:




[indent]

$criteria->together = true;

$criteria->with = array(

	'idIndividual'=> array(

                'select' => 'individual_registry',

		'joinType' => 'INNER JOIN',

                'with' => array(

                        'idPerson' => array(

                            'select' => 'person_full_name',

                            'on' => "personIndi.status = 'A'",

                            'joinType' => 'INNER JOIN',

                            'alias' => 'personIndi'

                        ),

                    ),

                ),

                

                'idCorporation' => array(

                    'select' => '',

                    'joinType' => 'INNER JOIN',

                    'with' => array(

                        'idPerson' => array(

                            'select' => 'person_full_name',

                            'on' => "personCorp.status = 'A'",

                            'joinType' => 'INNER JOIN',

                            'alias' => 'personCorp'

                        ),

                    ),

                ),

);


$criteria->addCondition("end_date_link IS NULL OR end_date_link >= '$now'");

$criteria->compare('start_date_link', '<=' . $now);

$criteria->addCondition("

     personIndi.person_full_name LIKE '%$searchText%' 

     OR personIndi.nickname LIKE '%$searchText%'

     OR idIndividual.individual_registry LIKE '%$searchText%'

");

[/indent]

And my code:




$commandBuilder = new CDbCommandBuilder(Yii::app()->db->schema);

$sqlLink = $commandBuilder->createFindCommand('link_corporation_individual', $criteria);

When I print the generated command it has no JOINS.

And o wanted the created join from my criteria, not create then all again on the new command.

Is it possible?