Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

[QUESTION] Create mapper-subselect #7

Open
MigasEu opened this issue Feb 14, 2019 · 4 comments
Open

[QUESTION] Create mapper-subselect #7

MigasEu opened this issue Feb 14, 2019 · 4 comments

Comments

@MigasEu
Copy link

MigasEu commented Feb 14, 2019

How can I create, from a MapperSelect, another MapperSelect to use in a join on the main query?

Right now I'm creating the 2 MapperSelects separated, then turning the second into a string and passing the bindvalues by hand.

The problem is that the bindings values colide, from the use of bindinline on both.

Something like:

/* @var $atlas \Atlas\Orm\Atlas */
$select = $atlas->select('table1')->(...);
$select2 = $atlas->select('table2')->(...);

$select2String = $select2->getStatement();
$select->join('INNER' , '('.$select2String.') as t2', 'table2.id = table.table2_id');
$bindValues = [];
foreach ($select2->getBindValues() as $bindKey => $bindValue) {
   $bindValues[$bindKey] = $bindValue[0];
}
$select->bindValues($bindValues);

Results in:

select
	*
from
	table1
inner join (
		select *
	from table2
	where
		table2.id = :__1__
		) t2 on t2.id = table1.table2_id
where
	table1.id = :__1__

Table1 has a relationship to table2, but instead of using a simple join, I want to use a subselect to keep separated scopes. This is useful especially because I'm reusing the 2nd select.

Thank you for the help.

@pmjones
Copy link
Contributor

pmjones commented Feb 14, 2019

Does this begin to help? http://atlasphp.io/cassini/query/select.html#1-4-3-3

@MigasEu
Copy link
Author

MigasEu commented Feb 14, 2019

Thank you for the fast response.

The problem is that we have 2 MapperSelects, not simple Select.
Is there a way to generate a subSelect that is a MapperSelect of a specific mapper class?

When I wrote $atlas->select('table1') I meant table1 as the Table1 Mapper class. This will give us a MapperSelect which allows us to transform the query using the relationships defined.

Again, thank you for your help.

@pmjones
Copy link
Contributor

pmjones commented Feb 14, 2019

Ah, I think I see. Yes, that's a tricky problem.

First, another suggestion, though it uses a JOIN proper and not a JOIN (SELECT ... AS) subselect. If you have defined relationships between the two mappers, you can do this: http://atlasphp.io/cassini/orm/direct.html#1-1-9-2

But I don't know that it suits your case. If it does not, then a question: Can you explain again why you feel it must be (or ought to be) a subselect instead of a typical JOIN ?

@MigasEu
Copy link
Author

MigasEu commented Feb 14, 2019

I have json+api REST service. The attributes/relationships are previously definedin metadata and the queries are generated base on that metadata, in this case, using atlas.

We define the base queries for each resource and when it's needed we join them.
For example, one of the resources has this base query:

/* @var $atlas \Atlas\Orm\Atlas */
$select = $atlas->select(Docheader::class)
                ->whereEquals([
            'docheader.status' => 3,
        ]);

On other resource (details) we want to join we this query, so it only shows details of orders with status=3.
When we used the joinWith approach, an alias is used, so instead of having the alias 'docheader' it has the alias 'document'. The where clause needs to change the alias.
If we use the subquery approach, we don't need to worry about this, we know what's there, we can just use it.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants