Thank you, Joel, for the fast response. Now for the $64,000 question, how can you do a join?
I have a company table, a person table and an employee table that links persons to companies through their primary keys. If I want to get a list of all employees for a company, I get the company id, use that to get a list of person ids from the employee table and use that list to get person objects one by one. What I'd REALLY like to do is a join of the person and employee table so I can get all the employees of a company and sort them by name in one operation.
I'm java programmer who's making his way to PHP so I'm not sure what is the best way to do this without breaking the object paradigm. I see two choices. First, define a virtual table in mysql that contains this join and create a new object to access this virtual table. Second, just get the array of person objects and write a sort routine for it. The first solution is elegant, the second solution is straight forward. Am I missing a third (or fourth or fifth) solution?
no, 1.6 will unfortunately not support joins. (and i don't know if or when this will be implemented)... Agreed, using your schema, and POG, getting a list of persons involves 3 steps as you've outligned.
However, in your case, if you're trying to get all the employees of a company, you could add a companyid field to your persons table as well. I agree that this introduces redundancy, which you're trying to avoid, but this companyid field would allow you to get the list of persons in one statement. And, in my opinion, one INT column isn't too bad in terms of overhead.
Thanks for your suggestions about possible ways to do the joins though, and I myself can't think of a 3rd, (or 4th) way. I need to think about it and see if and how POG could support this...
Well Joel, great minds must think alike. My original design did include a reference to the company table and did make this procedure easy. Unfortunately, there are persons who are not employees and employees carry a little extra information (when they were hired, fired, etc). So I finally gave in and decided to throw it all out and do the right thing with a cleaner design. Doing a join in sql is no big problem and that's probably what I'll do, I just hate to step outside these nice clean objects if I don't have to. I'm sure this is a difficult problem but I hope I can give you something to think about for the future.
> no, 1.6 will unfortunately not support joins. (and i don't know if or > when this will be implemented)... Agreed, using your schema, and POG, > getting a list of persons involves 3 steps as you've outligned.
> However, in your case, if you're trying to get all the employees of a > company, you could add a companyid field to your persons table as well. > I agree that this introduces redundancy, which you're trying to avoid, > but this companyid field would allow you to get the list of persons in > one statement. And, in my opinion, one INT column isn't too bad in > terms of overhead.
> Thanks for your suggestions about possible ways to do the joins though, > and I myself can't think of a 3rd, (or 4th) way. I need to think about > it and see if and how POG could support this...
