How to load an object based on unique contraint, and join with other table[s]?

Asked by George Kustas on 2011-03-21

I just downloaded cough, generated my classes, and now I am trying to use it. Here is my schema, simplified:

Account
------------
account_id
user_name
password
person_id
company_id

Person
---------
person_id
last_name
first_name

Company
------------
company_id
name
description

I want to find and load the account based on a user_name and company_id, which is defined as a unique contraint. That is, there will only be ONE such record since the user_name needs to be unique in a given Company. I also want the result to be a join with the Person and Company tables.

Do I need to implement a custom method to do all of this (including the SQL to perform the join)?

Not to be picky (because this is free), but it would be really helpful if someone were to post some sample code.I just can't find anything out there.

Thanks very much.

Question information

Language:
English Edit question
Status:
Answered
For:
CoughPHP Edit question
Assignee:
No assignee Edit question
Last query:
2011-03-21
Last reply:
2011-03-21
Anthony Bush (awbush) said : #1

True, we need examples for this (that aren't buried in the API docs):
http://coughphp.com/docs/1.3.5/api/cough/CoughObject.html#getFieldAliases

There are severals ways to get the desired result...

If you always want the construction of an Account object to pull the related joins, you can merely override the getLoadSql method by putting this in the Account class:

 public static function getLoadSql()
 {
  return '
   SELECT
    Account.*
    , ' . implode("\n\t, ", CoughObject::getFieldAliases('Person', 'Person_Object')) . '
    , ' . implode("\n\t, ", CoughObject::getFieldAliases('Company', 'Company_Object')) . '
   FROM
    Account
    INNER JOIN Person USING (person_id)
    INNER JOIN Company USING (company_id)
  ';
 }

Then anytime you retrieve an account, e.g. by user_name and company_id:

 $account = Account::constructByKey(array(
  'user_name' => $userName,
  'company_id' => $companyId,
 ));
 if (!is_object($account))
 {
  // no account found!
 }

these won't need to run extra queries because the data will already be in memory:

 $person = $account->getPerson_Object();
 $company = $account->getCompany_Object();

There's also a "load_sql_inner_joins" cough generation option that can be set to "enabled" which will build the appropriate getLoadSql() in the "*_Generated" class, but I prefer to leave this off and override methods on a case by case basis.

Anthony Bush (awbush) said : #2

(Please forgive the poor formatting... it would seem launchpad still does not easily support code samples here.)

George Kustas (gkustas) said : #3

Thanks Anthony - amazing response time!

I will look into this. I love the simplicity of your ORM, but I'm thinking that with all of the tables and relations I have, I may want to use the generation option you describe. I am still in the evaluation stage, and also looking at Doctrine right now. I realize that it is more "bloated" and complex than Cough - which is why I would still like to see what Cough is all about. I bagged ADODB and adodb_active record.

Thanks for your help.

Anthony Bush (awbush) said : #4

Awesome. Don't forget about Propel, it's another option.

Note that when using "enabled" for "load_sql_inner_joins" that Cough only generates the INNER JOINs when there are non-NULL foreign key constraints. So in your example, person_id and company_id would never be NULL and would always point to valid values in the related tables. Without this constraint, it's possible the INNER JOINs would fail to find an Account record that exists, which is why Cough won't add the joins in those cases. So, if you have an issue with it not generating the joins, make sure schema and foreign keys are correct first.

Best of luck!

George Kustas (gkustas) said : #5

Understood, Anthony. My DB schema is very tight, so this probably isn't an issue for me. I did review Propel and I am am still considering it. After looking through several reviews of both, it appears that Doctrine is a more popular choice, even though Propel has a "feel" to it that I am more familiar with. I come from a Windows .Net world, using LLBLGen and I like it. Any other opinions you have on the subject are greatly appreciated!

Can you help with this problem?

Provide an answer of your own, or ask George Kustas for more information if necessary.

To post a message you must log in.