Bdtjtk

In mysql I can create a query as:
select a.field1, b.field2, c.field2
from table1 a, table2 b, table2 c
where a.field2=b.field1
and a.field3=c.field1;

I need to be able to express this in Yii in a search function using the ActiveDataProvider.

I have tried the following:
...

     $dataProvider->setSort([

         'defaultOrder' => [

             'id' => SORT_ASC,

         ],

        'attributes' => [

            'id',

            'field1' => [

                'asc' => ['table1.field1' => SORT_ASC],

                'desc' => ['table1.field1' => SORT_DESC],

                'label' => 'Event Date'

            ],

            '1Name' => [

                'asc' => ['table2.field2' => SORT_ASC],

                'desc' => ['table2.field2' => SORT_DESC],

                'label' => 'Label 1'

            ],

            '2Name' => [

                'asc' => ['table2.field2' => SORT_ASC],

                'desc' => ['table2.field2' => SORT_DESC],

                'label' => 'Label 2'

            ],

...

This does cause two columns to be displayed, but the data is identical in both columns, whereas if the actual query as described above is run the data displayed is different between the two columns.

I can only find a solution by splitting the data in table2 into 2 distinct tables, but this is not an ideal solution and rejecting that difference in the search function 'attributes section' as:

...

     $dataProvider->setSort([

         'defaultOrder' => [

             'id' => SORT_ASC,

         ],

        'attributes' => [

            'id',

            'field1' => [

                'asc' => ['table1.field1' => SORT_ASC],

                'desc' => ['table1.field1' => SORT_DESC],

                'label' => 'Event Date'

            ],

            '1Name' => [

                'asc' => ['table2.field2' => SORT_ASC],

                'desc' => ['table2.field2' => SORT_DESC],

                'label' => 'Label 1'

            ],

            '2Name' => [

                'asc' => ['table3.field2' => SORT_ASC],

                'desc' => ['table3.field2' => SORT_DESC],

                'label' => 'Label 2'

            ],

...

Any thoughts, suggestions will be much appreciated.

To provide more clarity, the two tables of interest are anamain and anaesthetic.

anamain contains together with other fields,
anaesthetic1id
anaesthetic2id
both defined as INT.

anaesthetics, which contains 2 fields,
id, defined as INT
anaesthetic, defined as varchar(50).
These two table are linked by a one-to-many relationship between the anaesthetic.id field and the anamain.anaesthetic1id and anamain.anaesthetic2id fields.

Working directly in the database, the following sql returns the appropriate data:

select, b.anaesthetic AS 'First Anaesthetic',c.anaesthetic as 'Second Anaesthetic'
from anamain a, anaesthetic b, anaesthetic c
where a.anaesthetic1=b.id
and a.anaesthetic2=c.id;

This sql returns 2 columns with with different vales for each column for all records, which is what is expected.

My problem is in trying to implement this query via ActiveDataProvider in a search function in Yii.
I have tried the following code:

$dataProvider->setSort([

'defaultOrder' => [

'id' => SORT_ASC,

],

'attributes' => [

'id',

'anaesthetic1Name' => [

'asc' => ['anaesthetic.anaesthetic' => SORT_ASC],

'desc' => ['anaesthetic.anaesthetic' => SORT_DESC],

'label' => ‘First Anaesthetic'

],

‘anaesthetic'2Name' => [

'asc' => ['anaesthetic.anaesthetic' => SORT_ASC],

'desc' => ['anaesthetic.anaesthetic' => SORT_DESC],

'label' => ‘Second Anaesthetic'

],

I have defined the query as:

if (!($this->load($params) && $this->validate())) {

$query->joinWith(['anaesthetic']);

return $dataProvider;

}

This does return 2 columns 'First Anaesthetic' and 'Second Anaesthetic' via the DetailView::widget. However the data in both columns is the same for all records.

I have tried creating to versions of the model for the anaesthetics table - as anasethetic1.php and anaesthetic2.php and adjusting the query in the search function as

if (!($this->load($params) && $this->validate())) {

$query->joinWith(['anaesthetic1']);

$query->joinWith(['anaesthetic2']);

return $dataProvider;

}

This however throws a database error when the query is executed because both models are referencing the same underlying table.

Is there a way to replicate the use of aliases as in the pure database sql in to Tie.

I hope that this provides sufficient clarity.