Contain only when statement is true?

Need Help
1

Hii there,

I’m trying to get entries from the Comments table in my app.
Unfortunately, this doesn’t really work out right now as there are some “rules” to loading them than I can not get working by myself.

A the entities have to relevant objects: reply and status.
a reply is an association to another comment (to which the current comment is a reply) and the status is well… the status of the comment (eg. spam, private or public).
Now what I’m trying to do is selecting a comment by id:

$comment = $this->Comments->findById(15);

This is pretty easy, but here comes the tricky part: I want to contain the Reply but, I want to contain it only if the status is in the conditions array.
the conditions array is built like this:

$conditions [['status' => 1]];
if($this->user) {
            $conditions[]['user_id'] = $this->user->id;
}
// ... more conditions here

So this code should only contain a reply if the reply’s status is 1 or if the user is logged in, the user_id is the id of the logged in user.
I wonder how I should write this…

I have already come up with this piece of code, but this throw me an error:

Integrity constraint violation: 1052 Column ‘status’ in on clause is ambiguous

$comment = $this->Comments
        ->findById($this->request->getParam('id'))
        ->contain('Reply', function(Query $q) {
          $conditions = [['status' => 1]];

          // Check if the user is logged in
          if($this->user) {
            $conditions[]['user_id'] = $this->user->id;

            // Check if the user may view pending comments
            if($this->user->hasRight('view_pending_comments')) {
              $conditions[]['status'] = 0;
            }
        
            // Check if the user may view spam comments
            if($this->user->hasRight('view_spam_comments')) {
              $conditions[]['status'] = 2;
            }
          }

          return $q->where(['OR' => $conditions]);
        })
        ->first();

It results in the following SQL query:

SELECT Comments.id AS `Comments__id`, Comments.user_id AS `Comments__user_id`, Comments.reply AS `Comments__reply`, Comments.body AS `Comments__body`, Comments.posted AS `Comments__posted`, Comments.modified AS `Comments__modified`, Comments.status AS `Comments__status`, Reply.id AS `Reply__id`, Reply.user_id AS `Reply__user_id`, Reply.reply AS `Reply__reply`, Reply.body AS `Reply__body`, Reply.posted AS `Reply__posted`, Reply.modified AS `Reply__modified`, Reply.status AS `Reply__status` FROM comments Comments LEFT JOIN comments Reply ON (status = :c0 AND Reply.id = (Comments.reply)) WHERE Comments.id = :c1 LIMIT 1
2

That is the clue here :slight_smile: You need to give the DB server the full column name since in this case it doesn’t know which status column you are referring too. Replace all the column names in your closure function with $this->Comments->Reply->aliasField('status') (and $this->Comments->Reply->aliasField('user_id'))

3

I’m currently not at work so I can’t test it out yet.
But I think you mean something like this?

function(Query $q) {
  // ... Conditions stuff here
  
  $this->Comments->Reply->aliasField('status');
  $this->Comments->Reply->aliasField('user_id');
  return $q->where(['OR' => $conditions]);
}

But wouldn’t this result in it overwriting the objects “permanently” (or atleast until the next request)?

4

No, he means like this:

$conditions = [[$this->Comments->Reply->aliasField('status') => 1]];

Alternately, you could just use

$conditions = [['Reply.status' => 1]];

though that’s maybe more susceptible to breakage through changes elsewhere in your code.

1 Like
5

Thanks, I’ll have a look at it tomorrow :slight_smile:

How come?

6

Well, the former option uses system state to determine the name to use, while the latter is hard-coded. But then again the association chain ($this->Comments->Reply) is hard-coded too, so maybe it’s a wash. Couldn’t think of any specific scenarios where one would break and not the other, hence why I said “maybe”. :slight_smile:

I use the Reply.status form in my own code, as I find it more friendly for searches to find usage. And lately I’m trying to use the dot notation for all such references, whether or not it’s required to disambiguate, because things change and ambiguity can suddenly appear where there was none before, by adding a new field to a related table for example. A few extra keystrokes seems a small price to pay for the additional peace of mind.

1 Like
7

Tested it, seems to work :slight_smile: