| # |
Mar 14th 2018, 13:08 |
 lorenzo |
From your previous gist it is obvious that newExpr exists, just that you are calling it on something else |
| # |
Mar 14th 2018, 13:08 |
narendravaghela |
okay, no problem, I will paste the current code if does not work, so you can review |
| # |
Mar 14th 2018, 13:07 |
lorenzo |
Just review it and fix whatever possible mistake I made when writing it. |
| # |
Mar 14th 2018, 13:07 |
lorenzo |
Currently on my phone so can’t help a lot more, but what I sent should be enough |
| # |
Mar 14th 2018, 13:06 |
narendravaghela |
can you help me preparing that with queryBuilder please? |
| # |
Mar 14th 2018, 13:05 |
lorenzo |
you need a join with a subquery |
| # |
Mar 14th 2018, 13:05 |
narendravaghela |
and now I am struggling with that sql query in ORM :slightly_smiling_face: |
| # |
Mar 14th 2018, 13:04 |
lorenzo |
Yes |
| # |
Mar 14th 2018, 13:02 |
narendravaghela |
is it possible using single sql query? |
| # |
Mar 14th 2018, 13:01 |
narendravaghela |
my problem is, I have a TimeLogs table. I want to show all rows in table format, but also want to show the total logged hours for particular date |
| # |
Mar 14th 2018, 13:01 |
narendravaghela |
it still returns 11 rows |
| # |
Mar 14th 2018, 13:01 |
narendravaghela |
I tried to prepare a native sql query without using ORM to check whether if it works or not ``` SELECT si1.* FROM time_logs AS si1 JOIN (SELECT id, sum(id) FROM time_logs GROUP BY date) AS si2 ON si1.id = si2.id ``` |
| # |
Mar 14th 2018, 12:59 |
lorenzo |
and did you investigate where that error comes from? |
| # |
Mar 14th 2018, 12:53 |
narendravaghela |
@pierre-baptiste.varle use like `Router::url('http://www.google.com')` |
| # |
Mar 14th 2018, 12:50 |
pierre-baptiste.varle |
can we use Router::url() to generate link to external website (I would like to use this to manage query options of url) |
| # |
Mar 14th 2018, 12:49 |
narendravaghela |
it returns error `Method newExpr does not exist` |
| # |
Mar 14th 2018, 12:49 |
narendravaghela |
when I tried `innerJoin(['TL' => $subQuery]` |
| # |
Mar 14th 2018, 12:47 |
lorenzo |
There is no subquery there |
| # |
Mar 14th 2018, 12:47 |
lorenzo |
Of course |
| # |
Mar 14th 2018, 12:47 |
narendravaghela |
but this is not the desired sql output, it returns only 11 rows, but there are total 41 rows |
| # |
Mar 14th 2018, 12:46 |
narendravaghela |
@lorenzo I ended up with https://gist.github.com/narendravaghela/9f706ed532403f478d6bcd1f4abac527 |
| # |
Mar 14th 2018, 12:21 |
dakotairene |
:wave: |
| # |
Mar 14th 2018, 12:18 |
narendravaghela |
`Method newExpr does not exist ` |
| # |
Mar 14th 2018, 12:16 |
narendravaghela |
trying.. |
| # |
Mar 14th 2018, 12:16 |
lorenzo |
`innerJoin(['TL' => $subQuery], function ($q) { return return $q->newExpr()->equalFields('TL.id', 'TimeLogs.id'); })` |
| # |
Mar 14th 2018, 12:15 |
narendravaghela |
okay |
| # |
Mar 14th 2018, 12:15 |
lorenzo |
start by adding those |
| # |
Mar 14th 2018, 12:15 |
lorenzo |
well, you are missing the `having` and I told you to use `innerJoin` |
| # |
Mar 14th 2018, 12:15 |
narendravaghela |
this is native sql query to achieve all rows with groupby |
| # |
Mar 14th 2018, 12:14 |
narendravaghela |
```sql SELECT si1.* FROM sold_items AS si1 JOIN (SELECT member_id FROM sold_items GROUP BY member_id HAVING SUM(amount) > 50) AS si2 ON si1.member_id = si2.member_id |
| # |
Mar 14th 2018, 12:14 |
narendravaghela |
I am little bit confused here, not getting an idea of working solution |
| # |
Mar 14th 2018, 12:13 |
lorenzo |
why `leftJoinWith` ? |
| # |
Mar 14th 2018, 12:12 |
narendravaghela |
that is the goal |
| # |
Mar 14th 2018, 12:12 |
narendravaghela |
There is a TimeLogs table, I want all rows and also want the sum of hours group by particular date |
| # |
Mar 14th 2018, 12:12 |
narendravaghela |
@lorenzo here is gist - https://gist.github.com/narendravaghela/9f706ed532403f478d6bcd1f4abac527 |
| # |
Mar 14th 2018, 12:08 |
narendravaghela |
let me try myself, or I will share my code |
| # |
Mar 14th 2018, 12:07 |
lorenzo |
the last `where(...)` could be something like `where(function ($q) { return $q->newExpr()->equalFields('c1.id', 'Things.id')})` |
| # |
Mar 14th 2018, 12:07 |
narendravaghela |
@lorenzo thanks |
| # |
Mar 14th 2018, 12:06 |
lorenzo |
that’s how you join a subquery on the same contained table…. or whatever table |
| # |
Mar 14th 2018, 12:05 |
lorenzo |
@narendravaghela `$usersTable->find()->contain('Things')->innerJoin(['c1' => $commentsTable->find()->where(…)])->where(…)` |
| # |
Mar 14th 2018, 12:04 |
xinobi |
lorenzo exactly |