trying to do a "real" join

[pjacobs]

Hey,

I have a suggestion, maybe you could make autocrud do a real join so when I make a relationship between tables I can filter criteria depending on data of a foreign field. I think it's a huge performance advantage.

Here's my scenario, just to give a bit of background on what I'm trying to do, and why I know It doesn't do what I want it to do.

I have a one to many relationship between two tables. The first is a table called companies, which is a list of companies. In relation to said table, there is a table of addresses. Each company can have many addresses. I would like to be able to do a query listing every company in a certain location. So maybe every company in the state of Texas.

I try:
$autoCrud->Address->where = "state = 'tx'";
$autoCrud->Company->orderBy = 'AlphaSort';

OR

$autoCrud->Company->where = "Address.state = 'tx'";
$autoCrud->Company->orderBy = 'AlphaSort';

$results = $autoCrud->Company->select();
print_r($results);

The results in the first come through with no filtering for the address criteria. The second one comes in an error since there is no "real" join, which ouputs the query. I understand that I could do two sperate queries, but truthfully, the performance hit dosn't compensate for my use of this library in a production enviornment.

Oh, and the same issue happens when you want to paginate. I would really stick to defining limit. Maybe extend the application with pagination at a later date. All in all good work mate, please let me know if you come up with a fix

[Dennis Pallett]

Unfortunately it's not (yet) possible to use fields from the other table in the WHERE clause, because it's not a real join, like you say. I'm hoping the next version will solve this problem (it's annoyed me as well).