Table aliasing with the new ->join

helmm

I've been doodling with the new ->join functionality in Editor 1.2 and I have not been able to figure out how to get an alias to take. I thought it would be the name option but that has not done it so far. I have a table of publishers with (idPublisher, Publisher, Publisher_Parent) the publisher parent contains a idPublisher number for grouping purposes. Instead of the # showing in the table I wanted to show the actual name using a query similar to the following.

[code]SELECT p2.Publisher
FROM publisher as p2
JOIN publisher` on (publisher.idPublisher = p2.idPublisher)[/code]

This is the last editor::inst that I used.
[code]
$editor = Editor::inst( $db, 'publisher', 'idPublisher' )
->field(
Field::inst( 'Publisher' )->validator( 'Validate::required' ),
Field::inst( 'Publisher_Parent' ),
Field::inst( 'Publisher_Code' )
)
->join(
Join::inst( 'publisher', 'object')
->name('p2')
->join( 'idPublisher', 'idPublisher' )
->field( Field::inst( 'Publisher' )
)
);
[/code]

Any input would be appreciated.

allan

Excellent question. I'm afraid to say that this particular scenario wasn't considered in the design of the current Join class - indeed I think it goes a bit deeper than that - the Database classes would need to consider this ability to alias tables, which they currently don't.

I have this marked down as a feature to be implemented for the next update - sorry I don't have an immediate solution for you.

The best suggestion I can make at the moment is a read-only one - don't use `Join` but rather when 'getting' data, loop over the data that is retrieved and for each query the database (or used a cached query result) and assign the extra information to the data rows. But as I say, that would only work for read only - write as well would require a custom controller to be written.

Regards,
Allan

helmm

Hmm thanks for this read only would probably work as I need the name displayed in the datatable not the actual editor portion.

allan

For anyone else reading this thread, this feature will be included in Editor 1.2.1 which is shortly due for release. The new method `aliasParentTable` is the key to getting this to work - please see the Editor documentation for further information.

Regards,
Allan

helmm

Editor 1.2.1 joins work quite nicely. One question. I notice in query.php your join function can accept a join type but I'm not seeing where we can pass that along. Am I missing something?

allan

No you aren't missing anything - although that base functionality is avaibke in the database layer, it isn't exposed by the Editor Join class as the Join class always assumes an inner join. If you want to use a different SQL join type, then the best way would probably be to implement something that is specific to your use case.

Regards,
Allan