1

How can I select only distinct records, from relational table, when using with() operator in Yii?

I'm getting my models (records) like that:

$probe = Probes::model()->with(array
(
    'user',
    'results',
    'results.answer',
    'survey',
    'survey.questions',
    'survey.questions.question',
    'survey.questions.question.answers',
    'manager'
))->findByPk($id);

I want to make sure, that survey.questions relation returns only distinct records. But it seems, that I don't see any way to achieve this (or I'm blind / not educated enough).

When giving relational table name / alias as array:

'results.question'=>array('alias'=>'results_question'),

the distinct key is not among those, that can be used in such array (as modifier).

I tried very ugly, bumpy way of changing select from default * to DISTINCT *:

'survey.questions'=>array('select'=>'distinct'),

But this has (of course?) failed:

Active record "SurveysQuestions" is trying to select an invalid column "distinct". Note, the column must exist in the table or be an expression with alias.

How can I achieve this (seemed so obvious and easy), if it is possible at all this way (using with())? If not, then -- please, advice how to get distinct records in relational table any way (other than manually filtering results using foreach, what I'm doing right now, and what is ugly).

trejder
  • 17,148
  • 27
  • 124
  • 216
  • 1
    Seems that query builder consider distinct as a column. Try to specify fields after it: array('select' => 'distinct id, ..., ..., etc') – Andrii Mishchenko Apr 25 '14 at 15:30
  • @AndreyMischenko Nope, no change (I'm not surprised). `Active record "SurveysQuestions" is trying to select an invalid column "distinct *"` or `Active record "SurveysQuestions" is trying to select an invalid column "distinct id"` etc. this time! :[ – trejder Apr 25 '14 at 22:15

1 Answers1

1

You could set CDbCriteria::distinct to true:

'survey.questions'=>array('distinct'=>true),
topher
  • 14,790
  • 7
  • 54
  • 70
  • 1
    I'm accepting your answer, which does answers my question. However, I made the same mistake, as [this guy](http://stackoverflow.com/q/6127338/1469208). And from [an answer](http://stackoverflow.com/a/6127471/1469208) to his question, I figured out, that I mistaken `DISTINCT` with `GROUP BY`. Knowing this, I managed do solve my problem. Unfortunately, basing on auto-generated alias, which isn't something, that suits me. So... maybe you can help -> [here](http://stackoverflow.com/q/23304409/1469208). – trejder Apr 25 '14 at 22:59