Writing SQL queries in a Yii-friendly way

[jazzypants (Jazzypants18)]

I’ve stepped away from my Yii project for far too long, and I’ve since found that I have forgotten all I once knew about writing Yii-friendly queries. Would anyone be able to refresh my memory and describe how the following SQL query could be written in a Yii-friendly way? I’m trying to get one TeammateAvailability record for each Teammate for a particular Event, with the caveat that some teammates may not have recorded their availability yet. In other words, each Teammate can have zero or one TeammateAvailability records for a given event.

The query I have is something like this:

SELECT t.teammateId, ta.*

FROM Teammates t

LEFT OUTER JOIN TeammateAvailability ta ON ta.teammateId = t.teammateId AND ta.eventId = :1

WHERE t.teamId = :2

What I’m looking for is something like this:

$availArray = TeammateAvailbility::model()->with(foo)->findAll(bar)...

If anyone needs more information on the database schema and all that, I can certainly provide it. I just figured this would probably be easily solved by a person with any clue about Yii-friendly query writing.

Thanks in advance!

[mikl (Mike)]

Sorry for being a little harsh: But why don’t you just check out the guide? Don’t mix up the forum with Google search bar - it’s no fun to answer the same basic questions again and again (even worse if the questioner seems only to be too lazy to RTFM).

http://www.yiiframew…en/database.arr

EDIT:

Forgive my unkind words above, your post cought me on the wrong foot this morning.

But i still believe you already know the answer and also how you could solve the remaining questions on your own.

[jazzypants (Jazzypants18)]

Hmm… I thought it was a given that people who post questions on forums have already looked for answers elsewhere. I didn’t think I had to explicitly state that I had read through the guide prior to posting my question (along with the comments contained therein), and unfortunately didn’t find the answer I was looking for. I’ll make sure to explicitly write that next time, so as not to anger the forum gods.

Let me try this another way…

I could totally have missed it, but I didn’t see anything in the guide that explicitly mentioned how to map the following portion of the query:

LEFT OUTER JOIN TeammateAvailability ta ON ta.teammateId = t.teammateId AND ta.eventId = :1

The relation between Teammate and TeammateAvailability is always present through teammateId, but the inclusion of the limitation by eventId is only present in this particular query. I’m not trying to change the relation that is already there… simply add this portion of the relation within the join for this particular query. Can someone explain how they would approach this in Yii?

Thanks again.

[mikl (Mike)]

Ok, so how about this approach then:

class Teammates extends CActiveRecord

{

  //...


  public function relations()

  {

    return array(

      // ...

      'availabilities'=>array(self::HAS_MANY,'TeamAvailabilites','teammateId','on'=>'availabilities.eventId=1');

    );

 //...

}

Teammates::model()->with('availabilites')->findByPk($id);

[mikl (Mike)]

Or, if it’s just for a single query, forget about the ‘on’ in relation definition and simply do:

Teammates::model()->with(array('availabilites'=>'on'=>'availabilites.eventId=1'))->findByPk($id)

[jazzypants (Jazzypants18)]

Ah, so in the latter example, the “on” applies only to the “availabilities” relation, not to the entire query, right? That’s the part I was missing. My attempt to add that condition to the entire query was not having the desired effect. I’ll give that a shot. Thanks so much!

PS - Also, thanks for editing your original post. The fact that you took a minute to do so was the difference between me thinking you just had a bad moment while responding to my poorly worded question and me thinking you were just being an angry jackass.

[mikl (Mike)]

After all, we’re still only humans …

Oh, and i see now, there’s an ‘array(…)’ missing in my second example. Can you find where …?

[jazzypants (Jazzypants18)]

Yup, I noticed the issue with the missing "array()"… all set now.

Now that this is working for me, I ran into a slightly different, yet related question. In some other frameworks I’ve used, I’ve enjoyed the benefit of the following behaviour, described using my example… When a teammate has not created an availability record for a particular event, I can still access the properties of the availability object. I can refer to said properties by saying something like “$teammatesArray[2]->availabilities[0]->myProperty”. When I do this, I obviously just get null as a result. This is very convenient because I don’t have to write any conditional logic when working with availability records. If an availability record does not exist, the “myProperty” element is just null. This is most similar to how this would work if I was accessing a SQL result set, as any missing properties just show up as null.

This works differently in Yii. In the Yii manual, it states the following:

Info: If there is no related instance for a relationship, the corresponding property could be either null or an empty array. For BELONGS_TO and HAS_ONE relationships, the result is null; for HAS_MANY and MANY_MANY, it is an empty array. Note that the HAS_MANY and MANY_MANY relationships return arrays of objects, you will need to loop through the results before trying to access any properties. Otherwise, you may receive "Trying to get property of non-object" errors.

So, in my example, when a teammate does not have an availability record for a given event, “$teammatesArray[2]->availabilities” is an empty array. This is exactly what the manual says should happen, but I was hoping to be able to do something like “$teammatesArray[2]->availabilities[0]->myProperty” and just get null as a result. Is there any way to achieve the sort of result I’m looking for with some sort of options or configuration, or is this just something that is fundamentally different in Yii? Like I said, its not the end of the world if its can’t be done, but I wanted to make sure I wasn’t misunderstanding something about Yii before I started writing potentially unnecessary conditional logic in my app to handle it.

Thanks in advance.

[mikl (Mike)]

I can’t think of an easy way to workaround circumvent this. So i’d say: yes, this is a fundamental difference.

EDIT: English is not perfect yet, some words are so obvious but still missing. I hope for English 2.0 …

[trejder (Tomasz)]

Offtopic…

I hope for English 2.0

Mike, you’re killing me! English 2.0?

And what is your idea for getting up on wrong foot and get that level of good humour? I would be very pleased to copy it (with all copyrights leftover! , because I get up that bad from time to time and can’t think over anything more pleasant than punching my boss directly into his annoying face!

[mikl (Mike)]

I would be very pleased to copy it (with all copyrights leftover! , because I get up that bad from time to time and can’t think over anything more pleasant than punching my boss directly into his annoying face!

Actually that’s exactly what i did!

… just kidding

It’s really OT, but interesting nevertheless: A lot of “nerds” i met so far either suffer from somehow being introverted, carrying around a lot of anger or having a depressed nature (myself included, of course). These attributes might in some way be helpful if you work with machines (that’s why we got nerds in the first place). But they are not really helpful in making your and your fellows life a happy experience. What I had to learn: You don’t have to live with that! Humour is one way you can fight it, but there’s surely more, like being more aware. But that’s really getting way OT now …