staudenmeir / laravel-cte Goto Github PK
View Code? Open in Web Editor NEWLaravel queries with common table expressions
License: MIT License
Laravel queries with common table expressions
License: MIT License
This is a suggestion. I would appreciate if you add the SkipLocked function to the Query Builder.
insertUsing() should return the number of rows inserted. It now returns true instead. I'm guessing this is an old laravel bug that was copy/pasted into laravel-cte?
laravel-cte's Builder:
return $this->connection->insert(
laravel's Builder:
return $this->connection->affectingStatement(
There are also other differences in the two functions, but I am not familiar enough with the Builder to say if they're intentional or not. laravel does applyBeforeQueryCallbacks() in pretty much all of the functions.
There are issues with https://github.com/tpetry/laravel-postgresql-enhanced:
Tpetry\PostgresqlEnhanced\Query\Grammar::prependCtes(): Argument #1 ($query) must be of type Tpetry\PostgresqlEnhanced\Query\Builder, Staudenmeir\LaravelCte\Query\Builder given, called in vendor/tpetry/laravel-postgresql-enhanced/src/Query/Grammar.php on...: Tpetry\\PostgresqlEnhanced\\Query\\Grammar::prependCtes(): Argument #1 ($query) must be of type Tpetry\\PostgresqlEnhanced\\Query\\Builder, Staudenmeir\\LaravelCte\\Query\\Builder given, called in vendor/tpetry/laravel-postgresql-enhanced/src/Query/Grammar.php on line 54 at vendor/tpetry/laravel-postgresql-enhanced/src/Query/GrammarCte.php:36)
Hi Jonas,
Follow-up regarding #27, I've tried above and the following is the response:
array:31 [
0 => {#1343
+"created_date": "2021-09-03"
+"successCalls": "0"
+"failedCalls": "0"
}
1 => {#1344
+"created_date": "2021-09-04"
+"successCalls": "0"
+"failedCalls": "0"
}
2 => {#1345
+"created_date": "2021-09-05"
+"successCalls": "0"
+"failedCalls": "0"
}
.
.
.]
But when I run the actual code, then got only:
array:1 [
0 => array:3 [
"created_date" => "2021-10-12"
"successCalls" => "0"
"failedCalls" => "0"
]
]
Following is the Actual Code:
$subQuery = DB::table('tbl_calls')->selectRaw("DATE_FORMAT(created_at,'%d-%M-%Y') AS created_date, SUM(IF((dial_call_status = 'completed' OR dial_call_status = 'recording'), 1, 0)) AS successCalls, SUM(IF(dial_call_status = 'no-answer', 1, 0)) AS failedCalls")
->where([
// ['user_id', $userId],
['created_at', '<=', $startDate],
['created_at', '>=', $endDate],
// ['campaign_name', 'like', "%$search%"]
])
->unionAll(
DB::table('date_cte')
->selectRaw('created_date, 0, 0')
)
->groupBy('created_date');
TblCalls::withRecursiveExpression('date_cte', function($query) use ($startDate, $endDate) {
$query->selectRaw("CAST('$startDate' AS DATE) AS created_date")
->unionAll(
DB::table('date_cte')
->selectRaw("ADDDATE(created_date, INTERVAL 1 DAY)")
->where([['created_date', '<', "'$endDate'"]])
);
})
->selectRaw("created_date, successCalls,failedCalls")
// ->from(DB::raw(' ( ' . $subQuery->toSql() . ' ) AS X '))
->fromSub($subQuery, 'X')
->groupBy('created_date')
->get()->toArray();
Please help. Sorry for the late response.
This is not a bug, but and enhancement/idea. I was wondering if it would be possible to have a flag or string based config to determine if we want all builders to be Staudenmeir\LaravelCte\Query\Builder
on app load or if we want to manually specify when we want to use this specific builder type by the help of a specific Builder class (or maybe a trait?). For example (I'd leave the naming up to you):
CteBuilder::from('tree')
->withRecursiveExpression('tree', $query)
->get();
I currently don't like the fact that all builders are overwritten because it makes the package a huge project dependency and most of my existing phpdoc comment blocks obsolete.
When trying to use php 7.4 with this library it throws the following error:
implode(): Passing glue string after array is deprecated. Swap the parameters
I use this package to load a bunch of specific records by multi column combinations instead of a query:
with ids (a, b) as (
VALUES ROW(534, 1), ROW(3804, 3), ROW(3804, 1), ROW(531, 10), ROW(531, 7)
)
select *
from site_checks
inner join ids on ids.a = site_checks.a and ids.b = site_checks.b
But I had to type the VALUES
and ROW
s myself. VALUES might be standard SQL, but ROW()
is db specific. MySQL wants it, SQLite doesn't, that's all I know.
Seems like a job for grammar? Maybe including the VALUES, or maybe not, or maybe even per ROW.
(I don't even know how to call grammar from my custom relationship, but I could probably find out 😄)
hi @staudenmeir
i'm looking into oracle since few days ago and after reading issue #31, i tried on my local with hints you provided in oracle-laravel-6 branch and i can confirm it is working fine.
just wondering, would you like to get oracle driver included in this package?
After migrating from version v1.0.7 to v1.5.3, a query builder cte expression is now broken.
I'm using Laravel v8.83.26.
This is the code:
$q = DB::table("table_b")->select(
DB::raw('distinct on col_1'),
'col_2',
'col_3',
)
->where('col_4', '=', 55)
self::withExpression('my_cte', $unitsToUpdate)
->join('my_cte', DB::raw('my_cte.col_4'), '=', 'table_a.col_4')
->update([
'col_1' => DB::raw('my_cte.col_3'),
]);
The think that made me wonder is what is this ctid
column so after some digging I found in compileUpdateWithJoinsOrLimit()
in vendor/laravel/framework/src/Illuminate/Database/Query/Grammars/PostgresGrammar.php
And the generated query is:
update
"table_a"
set
"col_1" = my_cte.grouping,
where
"ctid" in (with "my_cte" as (
select
distinct on
col_1,
"col_2",
"col_3",
from
"table_b"
where
"col_4" = 55)
select
"table_a"."ctid"
from
"table_a"
inner join "my_cte" on
my_cte.col_4 = "table_a".col_4);
Hey, I've discovered you package last week, Im tinkering with it right now, after installing mysql 8 (what a PITA), but i have a question, how would we use it as an eloquent relationship?, since I think the "belongsTo" and other relationships methods will not support it.
Thank you, I'll keep trying to make it work also, still learning mysql CTE.
The Laravel 8 commit 6da2778 bumped the version requirement, but there does not appear to be any code between the previous commit and now that actually needs laravel 8 to function. This change prevents this package from being added to Laravel 7.
A version requirement of ^7.0|^8.0
would allow installation on either.
I checked the changes via git diff 56794181c5e418efacb9f5fcd96a44aed6d9ab48...HEAD src
Hi,
I need help to make this package work with many to many relation.
I have three basic tables
categories
id | name | parent_id |
---|---|---|
1 | root | null |
2 | sub 1 | 1 |
3 | sub 2 | 2 |
products
id | name |
---|---|
1 | product 1 |
2 | product 2 |
3 | product 3 |
and category_product lets say
category_id | product_id |
---|---|
2 | 1 |
3 | 2 |
3 | 3 |
And I want to calculate how many products category has. So root would have 3 then sub1 would have also 3 and sub2 2 in this case.
What I have found on laracasts forum very similar solution but with different relation.
So now I have on Category model
public function countTotalProducts()
{
$query = DB::table('categories')->selectRaw('categories.*')->where('id', $this->id)->unionAll(
DB::table('categories')->selectRaw('categories.*')->join('tree', 'tree.id', '=', 'categories.parent_id')
);
$tree = DB::table('tree')->withRecursiveExpression('tree', $query)
->join('category_product', 'category_product.category_id', '=', 'tree.id')
->join('products', 'category_product.category_id', '=', 'tree.id')
->count('products.id');
return $tree;
}
In controller I do
$this->categories = Category::where('parent_id', null)->get()->each(function(&$item) {
$item->products_count = $item->countTotalProducts();
});
witch of course gives wrong count result.
I'm new to laravel and harden queries so I need your help to make it work.
Thanks.
Hi Firstly let me say a huge thanks for creating this brilliant package, we've been using it on a build and was looking forward to using the new toRawSql
feature that was merged in this week. After updating our code base and trying to use the method we get an error being thrown
The database driver's grammar implementation does not support escaping values.
After digging in and looking at the work that went into the Laravel PR I can see that there has been some updates to the way the grammar classes now set and how they use the current connection.
Dumping out the code just before it breaks in the new method I can see that the Laravel CTE Grammar class doesnt have a connection set.
Staudenmeir\LaravelCte\Query\Grammars\MySqlGrammar {#1741 ▼ // vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php:2644
#connection: null
#tablePrefix: ""
#operators: array:1 [▶]
#bitwiseOperators: []
#selectComponents: array:14 [▶]
}
after a quick look into this packages code I can see if I update the BuildsExpressionQueries
Trait in particular this method
protected function getQueryGrammar(Connection $connection)
{
$driver = $connection->getDriverName();
switch ($driver) {
case 'mysql':
return (new MySqlGrammar())->setConnection($connection);
case 'pgsql':
return new PostgresGrammar();
case 'sqlite':
return new SQLiteGrammar();
case 'sqlsrv':
return new SqlServerGrammar();
}
throw new RuntimeException('This database is not supported.'); // @codeCoverageIgnore
}
```
and use the new `setConnection` method this error then goes away and I can use the new method perfectly. I thought I would just let you know about the issue. Hopefully ive managed to explain the problem ok
I've tried manually adding the service provider and calling "php artisan package:discover" before calling the ide-helper generate command, none of which add auto-complete for this package. Also tried deleing the _ide_helper.php file then doing both of those steps mentioned and that also doesn't help. They show methods inside of the generated file like function query which returns a builder, but nothing gives me the auto-complete, not DB:: or DB::query or from a query builder or eloquent builder. Only thing that works is for eloquent models add the trait, but that is not the only use I need this package for.
Hello,
Is this any way this package can work with the OCI8 oracle driver.
https://github.com/yajra/laravel-oci8
Probably the largest database type not included. Currently just get "Call to undefined method" errors when trying to use.
As you know, MSSQL does not allow CTEs in sub queries.
Is there a way for the query to pass the expression up the chain so that it's applied at the beginning?
For my use case, I query a list that uses a CTE.
In reports, I need to aggregate calculations based on data in that list, so I throw the list query in a new subquery and apply selects.
The work around for this would be to pass the CTE query along with the base query so that I manually call the withExpression
method on the list query and then the aggregates query.
Figured I'd ask, just in case.
Often, for reasons I don't understand, type-hinting a closure as Eloquent/Builder fails because it's apparently been passed a CteBuilder instead.
Why does this happen when as far as I can tell the particular scope isn't using any CTE functions?
For example, this:
\App\Client::whereIn('SYSID', function($q) { dd($q); })->first();
tells me that $q is a CteBuilder.
Client doesn't use QueriesExpressions, extends normal Model class. I don't know why it would be insisting on using the CTE builder in that function.
public function compileExpressions(Builder $query)
{
if (! $query->expressions) {
return '';
}
$recursive = $this->recursiveKeyword($query->expressions);
$statements = [];
foreach ($query->expressions as $expression) {
$columns = $expression['columns'] ? '('.$this->columnize($expression['columns']).') ' : '';
$statements[] = $this->wrap($expression['name']).' '.$columns.'as ('.$expression['query'].')';
}
return 'with '.$recursive.implode($statements, ', ');
}
/**
* Get the "recursive" keyword.
*
* @param array $expressions
Arguments
"Undefined property: Illuminate\Database\Query\Builder::$expressions"
On Laravel 10, when trying to use leftJoinLateral:
https://laravel.com/docs/10.x/queries#left-join-right-join-clause
I get this error:
Call to undefined method Staudenmeir\LaravelCte\Query\Builder::leftJoinLateral()
My code looks like:
`
use Illuminate\Support\Facades\DB;
Route::get('/getfights', function(){
$sub = DB::query()
->fromSub(function($query){
$query->select('id')
->selectRaw('count(CASE WHEN winner_id = id THEN 1 END) as wins')
->selectRaw('count(*) - count(CASE WHEN winner_id = id THEN 1 END) - count(CASE WHEN winner_id is null THEN 1 END) as loss')
->selectRaw('count(CASE WHEN winner_id is null THEN 1 END) as other')
->fromSub(function($query){
$query->select('id', 'winner_id')
->from('events')
->orderBy('date', 'desc')
->take(5);
}, 'recent')
->groupBy('id');
}, 'recent')
->whereColumn('id', 'fighters.some_id');
$fighters = DB::table('fighters')
->leftJoinLateral($sub, 'recent')
->get();
});
`
ErrorException : Declaration of Staudenmeir\LaravelCte\Query\Grammars\CompilesExpressions::compileUpdate(Illuminate\Database\Query\Builder $query, $values) should be compatible with Illuminate\Database\Query\Grammars\Grammar::compileUpdate(Illuminate\Database\Query\Builder $query, array $values)
Using CTE's withExpression() method when SoftDeletes are active gives some problems. The temporary table created, does not know the 'real-table'.deleted_at. column
At this moment I use the withTrashed() method as a work-around to overcome this.
How should this be handled?
Hi,
This issue is more a question/discussion.
I integrated this repo to use the CTE functionality with query builder.
Besides CTE I also use some lateral joins. The lateral join clause is not supported by query builder but I created a repo that adds basic support for these type of joins.
Now to integrate both CTE and lateral joins in query builder I use these two repo's in a Laravel installation and add a custom service provider. But this service provider has to load a connection, and the connection loads the grammar implementations, and query builder, etc.. I also had to make a new builder and grammar class that inherit both CTE and lateral join logic (by using traits and inheritance).
In other words a lot of work to extend query builder with two 'add-ons' and it is not a very scalable solution.
Do you know about a more elegant way to extend query builder with multiple extensions in separated packages/repos?
Regards,
Chris
Hello,
I'm trying to have my IDE figuring out the added query builder's methods such as withExpression()
.
I've noticed that the return type of QueriesExpressions::newBaseQueryBuilder()
is set to \Illuminate\Database\Query\Builder
while it returns a Staudenmeir\LaravelCte\Query\Builder
object.
There was a PR fixing that typing issue which then was reverted by this PR: https://github.com/staudenmeir/laravel-cte/pull/50/files
It won't fix the IDE code completion when using DB
facades but at least it'll work when calling QueriesExpressions::newBaseQueryBuilder()
.
Hello,
Is this any way this package can work with the db2 driver.
https://github.com/cooperl22/laravel-db2
When i try this package, i have a "Call to undefined method Illuminate\Database\Query\Builder::withRecursiveExpression()"
I have the following MySQL query which I want to recreate using laravel-cte.
with recursive cte (id, comment, parent_id) as (
select id,
comment,
parent_id
from comments
where parent_id = 37
union all
select c.id,
c.comment,
c.parent_id
from comments c
inner join cte
on c.parent_id = cte.id
)
select * from cte;
Comments in my app can be children of other comments and in this query I am getting all of the child comments of the comment with the id 37, regardless of how deeply nested they are.
I'm fairly new to laravel and I'm having a hard time wrapping my head around your withRecursiveExpression()
example.
I came up with this but it returns all comments, not just children.
$query = DB::table('comments')
->where('parent_id', '37')
->unionAll(
DB::table('comments')
->select('comments.*')
->join('cte', 'cte.id', '=', 'comments.parent_id')
);
$comments = DB::table('comments')
->withRecursiveExpression('cte', $query)
->get();
Can you help me understand how to use laravel-cte with my use case?
This is only returning 2 rows, while the dates are 10 days apart, is there anything I'm missing? Thanks!
$e = EventDate::select(['date_from', 'date_until'])
->unionAll(
EventDate::selectRaw('DATE_ADD(date_from, INTERVAL 1 DAY), date_until')
->whereRaw('DATE_ADD(date_from, INTERVAL 1 DAY) < date_until')
);
$recursive = EventDate::from('t')
->withRecursiveExpression('t', $e)
->get()
->toArray();
I'm tried to using the package in my project to test it out, but when i use the Laravel chunk method on the query, i get the following error: "Column specified multiple times"
I checked the query and i noticed that this package and the Laravel chunk method, use the alias temp_table and this cause the error.
Thanks.
Laravel Version: 8.74.0
Php Version: 8.0
Error generated by phpunit when I run my test:
Illuminate\Database\QueryException: SQLSTATE[HY000]: General error: 1 recursive reference in a subquery: cte (SQL: with recursive "cte" as (select * from (select * from "comments" where "commentable_id" = 1) union all select * from (select "comments".* from "comments" inner join "cte" on "cte"."id" = "comments"."commentable_id")) select * from "cte")
I have tested this with query builder and Eloquent. My example with eloquent is slimmed down to match the example in your documentation. Both functions give the same error in my test but it works fine when I test my endpoint in Postman or through the web browser.
commentable_id
is the id of the parent.
trait Comments
{
function getComments($id)
{
$query = DB::table('comments')
->where('commentable_id', $id)
->unionAll(
DB::table('comments')
->select('comments.*')
->join('cte', 'cte.id', '=', 'comments.commentable_id')
);
return DB::table('cte')
->withRecursiveExpression('cte', $query)
->join('users','cte.author_id','=','users.id')
->select('cte.*', 'users.id as user_id', 'users.first_name as first_name', 'users.last_name as last_name')
->orderBy('created_at', 'DESC')
->get();
}
function getCommentsWithEloquent($id)
{
$query = Comment::where('commentable_id', $id)
->unionAll(
Comment::select('comments.*')
->join('cte', 'cte.id', '=', 'comments.commentable_id')
);
return Comment::from('cte')
->withRecursiveExpression('cte', $query)
->get();
}
}
Formatted MySQL queries:
/***********
This query runs when the page is viewed in a web browser
it works fine.
**********/
with recursive `cte` as (
(select * from `comments` where `commentable_id` = '37')
union all
(select `comments`.* from `comments` inner join `cte` on `cte`.`id` = `comments`.`commentable_id`)
)
select `cte`.*,
`users`.`id` as `user_id`,
`users`.`first_name` as `first_name`,
`users`.`last_name` as `last_name`
from `cte` inner join `users` on `cte`.`author_id` = `users`.`id`
order by `created_at` desc;
/***********
composer test generates the next query and gives error
Illuminate\Database\QueryException: SQLSTATE[HY000]: General error: 1 recursive reference in a subquery: cte
**********/
with recursive `cte` as (
select * from (
select *
from `comments`
where `commentable_id` = 1
)
union all
select * from (
select `comments`.*
from `comments`
inner join `cte` on `cte`.`id` = `comments`.`commentable_id`
)
)
select `cte`.*,
`users`.`id` as `user_id`,
`users`.`first_name` as `first_name`,
`users`.`last_name` as `last_name`
from `cte`
inner join `users` on `cte`.`author_id` = `users`.`id`
order by `created_at` desc;
When I run the mysql code from the phpunit error message in MySQL Workbench it gives me this error:
Error Code: 1248. Every derived table must have its own alias
When I give the derived tables an alias I get this error:
Error Code: 3577. In recursive query block of Recursive Common Table Expression 'cte', the recursive table must be referenced only once, and not in any subquery
I can run the MySQL code that the browser and Postman are using and it works as expected. When I remove the extra SELECT * FROM
code from the statement in the phpunit error and run it in Workench, it works fine.
Do you know why it generates different MySQL code in the test? This package works perfectly when I visit the site in my browser.
Please add support for Firebird.
Hello Guys,
I'm facing an issue with mergeBindings() with withRecursiveExpression(). Following is the query that I'm trying to execute, kindly please help...
TblCalls::withRecursiveExpression('date_cte', function($query) use ($startDate, $endDate) {
$query->selectRaw("CAST('$startDate' AS DATE) AS created_date")
->unionAll(
DB::table('date_cte')
->selectRaw("ADDDATE(created_date, INTERVAL 1 DAY)")
->where([['created_date', '<', "'$endDate'"]])
);
})
->selectRaw("created_date, successCalls,failedCalls")
->from(DB::raw(' ( ' . $subQuery->toSql() . ' ) AS X '))
->mergeBindings($subQuery->getQuery())
->groupBy('created_date')
->get();
Thanks in advance..
My model contains the SpatialTrait from Grimzy\LaravelMysqlSpatial and the HasRecursiveRelationships trait from your adjacency list package, but since 1.5.1, I am now getting this error:
Undefined property: Grimzy\LaravelMysqlSpatial\Eloquent\BaseBuilder::$unionExpressions in .../vendor/staudenmeir/laravel-cte/src/Query/Grammars/CompilesExpressions.php on line 87�
Undefined property: Grimzy\LaravelMysqlSpatial\Eloquent\BaseBuilder::$unionRecursionLimit in .../vendor/staudenmeir/laravel-cte/src/Query/Grammars/CompilesExpressions.php on line 91�
Do you have any idea how to fix this? Have temporarily downgraded back to 1.5 for now. Thanks a lot.
After requiring the package (via dependency from laravel-adjacency-list) my test suite stopped working throwing me an issue about geometry column types. It seems having this package with grimzy/laravel-mysql-spatial conflicts because both try to set the singleton 'db.factory'.
$this->app->singleton('db.factory', function ($app) {
return new ConnectionFactory($app);
});
Any ideas how to solve this? I need both packages.
I have a report based query that needs to return multiple sets of data for different time ranges w/ associated labels. Using CTE lets me define what data I want to query against while the UNION
s let me setup the specific ranges. This works great when working w/ MySQL directly.
When using this package to try and convert the query to Laravel, it does not seem possible to properly leverage UNION
queries. The with
statement gets included with the first select
, and as soon as a union is added Laravel wraps both the initial select
and the with
in another select
.
Minimal example:
$q1 = DB::table('example')->where('col', true);
$q2 = DB::table('example')->where('col', false);
$q1->withExpression('example', function ($q) {
$q->select('anything')->from('somewhere');
})
->union($q2)
->dump();
Output:
select * from (
with "example" as (select "anything" from "somewhere")
select * from "example" where "col" = 1
)
union
select * from (select * from "example" where "col" = 0)
Expected:
with "example" as (select "anything" from "somewhere")
select * from (select * from "example" where "col" = 1)
union
select * from (select * from "example" where "col" = 0)
Is there a way to get this package to always put the with
statement on the outermost part of the query?
Please help. I'm doing an eloquent selection for the list of categories with its Hierarchical Data and need to get the path names of categories to subcategories (eg. Electronics > Mobile Phones > Android Phones) but these path names not appearing on my GET method. Here is my code.
CONTROLLER
$query = "SELECT name as path
FROM merchant_branch_categories
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, CONCAT(cp.path, ' > ', c.name)
FROM category_path AS cp JOIN merchant_branch_categories AS c
ON cp.id = c.parent_id";
$merchantBranchCategoryListQuery = Merchant_branch_category::when(
$request->name,
function ($query) use ($request) {
$query->where('name', 'LIKE', '%' . $request->name . '%');
})
->when($request->merchant_branch_id,
function ($query) use ($request) {
$query->where('merchant_branch_id', intVal($request->merchant_branch_id));
})
->when($request->parent_id,
function ($query) use ($request) {
$query->where('parent_id', intVal($request->parent_id));
})
->when($request->description,
function ($query) use ($request) {
$query->where('description', 'LIKE', '%' . $request->description . '%');
})
->when($request->tags,
function ($query) use ($request) {
$query->where('tags', 'LIKE', '%' . $request->tags . '%');
})
->when($request->is_active,
function ($query) use ($request) {
$query->where('is_active', $request->is_active);
})
->withRecursiveExpression('category_path', $query, ['id', 'title', 'path']);
if ($request->per_page && $request->per_page != 'all') {
$merchantBranchCategoryList = $merchantBranchCategoryListQuery->with('categories')->paginate($request->per_page);
$merchantBranchCategoryList->appends($request->all());
} else {
$merchantBranchCategoryList = $merchantBranchCategoryListQuery->with('categories')->get();
}
return $this->sendResponse($merchantBranchCategoryList, 'Merchant Branch Category List Retrieved.');
I've tried to check the SQL string for $merchantBranchCategoryList (eg. $merchantBranchCategoryListQuery->toSql()) but the ACTUAL return is
with recursive category_path (id, title, path) as (SELECT name as path FROM merchant_branch_categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.name, CONCAT(cp.path, ' > ', c.name) FROM category_path AS cp JOIN merchant_branch_categories AS c ON cp.id = c.parent_id) select * from merchant_branch_categories
I must be expecting
with recursive category_path (id, title, path) as (SELECT name as path FROM merchant_branch_categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.name, CONCAT(cp.path, ' > ', c.name) FROM category_path AS cp JOIN merchant_branch_categories AS c ON cp.id = c.parent_id) select * from category_path
PLEASE HELP ME TO UNDERSTAND.
https://flareapp.io/share/V7jdkGom
I'm using this package in combination with https://github.com/tpetry/laravel-postgresql-enhanced. Is this a conflict?
Hi there, I have the following data structure
CREATE TABLE `Channels` (
`id` INT(11) AUTO_INCREMENT PRIMARY KEY,
`title` VARCHAR(255) NOT NULL,
`views` INT(11) NOT NULL DEFAULT 0,
) Engine = InnoDB;
INSERT INTO Channels (title, views)
VALUES ('Movies', 50030),
('TV shows', 51201),
('Howtos', 18601);
CREATE TABLE `Videos` (
`id` BIGINT AUTO_INCREMENT PRIMARY KEY,
`channel_id` INT(11),
`name` VARCHAR(255) NOT NULL,
`description` TEXT,
`views` INT(11) NOT NULL DEFAULT 0,
`created_at` DATETIME NOT NULL,
`updated_at` DATETIME,
CONSTRAINT videos_channel_id_fk
FOREIGN KEY ( channel_id )
REFERENCES Channels ( id )
) ENGINE=InnoDB;
INSERT INTO Videos (`channel_id`, `name`, `created_at`)
VALUES (1, 'A', '2019-10-03 00:00:12'), (1, 'B', '2019-03-01 00:12:12'),
(1, 'C', '2019-10-01 19:12:12'), (1, 'D', '2019-10-03 01:55:55'),
(2, 'E', '2019-10-03 01:58:00'), (2, 'F', '2019-10-01 15:11:11'),
(2, 'G', '2019-09-09 11:11:11'), (2, 'H', '2019-08-01 12:53:10');
and am using the following CTE
WITH cte AS (SELECT *,
ROW_NUMBER() OVER (PARTITION BY channel_id
ORDER BY created_at DESC) rn
FROM Videos)
SELECT *
FROM Channels c
LEFT JOIN cte ON c.id = cte.channel_id AND cte.rn = 1
I tried writing it as follows:
return DB::table($this->table. ' AS C')
->select(['cte.*'])
withExpression('cte', DB::table('Videos')
->select(['*',
DB::raw('ROW_NUMBER() OVER (PARTITION BY channel_id ORDER BY created_at DESC) AS rn')]))
->leftJoin('cte', 'Channels.id', '=', 'cte.channel_id')
->where('cte.rn', '=', 1)
but no matter what I've tried I always get the following error message:
Illuminate\Database\QueryException: SQLSTATE[42000]: Syntax error or access violation:
1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns
is illegal if there is no GROUP BY clause (SQL: with cte as (select , ROW_NUMBER()
OVER (PARTITION BY channel_id ORDER BY created_at DESC) AS row_number from
Videos) select Channels. from Channels left join cte on Channels.id = cte.channel_id
where cte.rn = 1) in file /Users/mark/Workspace/api.site/vendor/laravel/framework/
src/Illuminate/Database/Connection.php on line 665
I don't understand if 'tree' here is an actual model name provided by us or the library?
Currently, the newBaseQueryBuilder in QueriesExpressions has a return type of '\Illuminate\Database\Query\Builder'. Wouldn't it be beneficial to be more precise about the the exact return type and specify 'Staudenmeir\LaravelCte\Query\Builder'? This way, static code analysis tools and IDEs will actually pick up the additional methods provided by the extended class.
I'd be happy to create a pull request if you'd like.
I'm using your example to create a recursion using 'tree' as the recursion name, now I'm getting the following error
Column not found: 1054 Unknown column 'tree.date_from' in 'field list'
It seems like it's looking for that 'tree' table, but that's just the name of recursion, right?
$t = $query->from('tree')
->withRecursiveExpression('tree',
$this
->select(['id', 'event_id', 'date_from as date_from', 'date_from as date_until', 'date_until as _du'])
->unionAll(
$this->select([
'id',
'event_id',
DB::raw('DATE_ADD(tree.date_from, INTERVAL 1 DAY)'),
DB::raw('DATE_ADD(tree.date_from, INTERVAL 1 DAY)')
])
)
);
hello, first of all thank you for your work.
I ran into this error, is there a way to solve it?
When trying to register, this code gives the error Cannot use object of type Staudenmeir\\LaravelCte\\Connections\\MySqlConnection as array
, but if you remove the transaction, the code will work as it should.
final class Register
{
private User $user;
/**
* @throws ValidationException
*/
public function __invoke($rootValue, array $args): User
{
Validator::make($args['input'], [
'username' => ['required', 'regex:/^[a-zA-Z0-9]+$/u', 'min:2', 'max:16', 'unique:users'],
'email' => ['required', 'email', 'max:35', 'unique:users'],
'password' => ['required', 'min:8'],
'password_confirmation' => ['required', 'same:password'],
])->validate();
DB::transaction(function ($args) {
$this->user = User::create([
'username' => $args['input']['username'],
'email' => $args['input']['email'],
'password' => Hash::make($args['input']['password']),
]);
if ($this->user->id === 1) {
$this->user->update([
'role' => 'administrator',
'email_verified_at' => Carbon::now(),
]);
} else {
event(new Registered($this->user));
}
});
return $this->user;
}
}
Hello!
How to set 'materialized' option to an expression (cte)? Ty
I got this error:
[2020-07-08 12:09:02] local.ERROR: implode(): Passing glue string after array is deprecated. Swap the parameters {"userId":1,"exception":"[object] (ErrorException(code: 0): implode(): Passing glue string after array is deprecated. Swap the parameters at /var/www/html/vendor/staudenmeir/laravel-cte/src/Query/Grammars/CompilesExpressions.php:41)
[stacktrace]
#0 [internal function]: Illuminate\\Foundation\\Bootstrap\\HandleExceptions->handleError(8192, 'implode(): Pass...', '/var/www/html/v...', 41, Array)
#1 /var/www/html/vendor/staudenmeir/laravel-cte/src/Query/Grammars/CompilesExpressions.php(41): implode(Array, ', ')
...
Can't find any clue if it is due my specific use case and the query i'm building or what, but it looks like is a pure php related error and how the implode()
function is used.
Hi, I'm trying to use your package in a laravel 5.5 app with mssql 2016 on the background and it works fine until I don't try to use it with the query builders pagination. The initial page loads without a problem but when I click on the second page it fails with the error: SQLSTATE[42000]: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near the keyword 'with'. (SQL: select * from (with [orders] ([OrderId]) as ...
. I think the problem is that the builder wraps the whole select while adding the offset statement breaking mssqls syntax of WHERE statement.
Could You please check on this? Thank You in advance! :)
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.