Hi Jaroslaw!
On 08/03/2007 12:32 AM Jaroslaw Heba wrote:
> I'm using ezcPersistenObject to get data from database.
> Mostly is enough if I use createFindQuery to search objects with special
> conditions but then I can use conditions only from defined table (defined
> by using class name).
> I mean if I have table db_project in my database and defined class named
> Project for this table too, then I can use only conditions related to this
> table e.g.
> $qp = $session->createFindQuery( 'Project' );
> $qp->where(
> $qp->expr->lAnd(
> $qp->expr->eq( 'owner', $qp->bindValue( $specifiedOwnerID ) ),
> $qp->expr->eq( 'resource', $qp->bindValue( $specifiedResourceID )
> )
> )
> );
> $projects = $session->find( $qp, 'Project' );
> where 'owner' and 'resource' are members of db_project table.
> Hardly ever i need define conditions for more then one table and in this
> case i cannot use createFindQuery() (or maybe i can? Tell me if i'm
> wrong).
> I have figured out i can use createSelectQuery() for this purpose. e.g. i
> need projects which are related to some special type of resources
> (resourcetype member of db_resource table).
> $projectClassDef = $session->definitionManager->fetchDefinition( 'Project' );
> $qp = $db->createSelectQuery();
> $qp->select( $session->getColumnsFromDefinition( $projectClassDef )
> )->from( array( 'db_project', 'db_resource' ) );
> $qp->where(
> $qp->expr->lAnd(
> $qp->expr->eq( 'projectid', 'project' ),
> $qp->expr->eq( 'resourcetype', Resource::RESOURCE_MEMBER )
> )
> );
>
> $projects = $this->_psi->find( $qp, 'Project' );
> Ok, it should works well but.....
> What will happen if the same member name exists in db_project and
> db_resource tables?
> How avoid abiguity in this situation?
You can simply use a join in the shown case, there is no need to create
the select query yourself. Try using:
$qp = $session->createFindQuery( 'Project' );
$qp->from( 'db_resource' );
$qp->where(
$qp->expr->lAnd(
$qp->expr->eq( 'projectid', 'project' ),
$qp->expr->eq( 'resourcetype', Resource::RESOURCE_MEMBER )
)
);
That said for the first part. I missed a bit what your shown query
actually should do, maybe you can enlighten me? I thing the join
condition is missung, so you would get the carthesian product of all
records in db_resource that are of type RESOURCE_MEMBER and the selected
project from db_project. That would result in multiple records
containing the same project and therefore multiple times the same
Project object. Or am I wrong?
> Normaly aliases for tables should solve the problem. Is there any way to
> define aliases?
Maybe I got the actual problem wrong? I don't see how aliases could help
here? Anyway, you can use $qp->setAliases() [1] to define custom aliases.
> Of course i can define array which contains member names with tables
> aliases instead of using $session->getColumnsFromDefinition(
> $projectClassDef ) but it could be annoying if i have really long tables.
> Maybe it is not bad idea to add parameter to
> $session->getColumnsFromDefinition() which activate or inactivate aliases.
> I mean:
> $session->getColumnsFromDefinition( 'Project', true ) - aliases ON
> $session->getColumnsFromDefinition( 'Project' ) - aliases OFF
> As alias can be used class name or sth like this.
> Any better ideas to solve this problem?
Hmmm... I still don't get what the purpose should be. I think I
missunderstood the whole problem. Maybe you can elaborate a bit more?
Regards,
Toby
[1]
http://ez.no/doc/components/view/trunk/(file)/Database/ezcQuery.html#setAliases
--
Mit freundlichen Grüßen / Med vennlig hilsen / With kind regards
Tobias Schlitt (GPG: 0xC462BC14) eZ Components Developer
[EMAIL PROTECTED] | eZ Systems AS | ez.no
--
Components mailing list
[email protected]
http://lists.ez.no/mailman/listinfo/components
