usmanhalalit / pixie Goto Github PK
View Code? Open in Web Editor NEWDatabase query builder for PHP, framework agnostic, lightweight and expressive.
License: MIT License
Database query builder for PHP, framework agnostic, lightweight and expressive.
License: MIT License
hi, @usmanhalalit
Whether to support the transaction?
Hello,
I just noticed that by default QueryBuilderHandler
always sets this:
$this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
Why is this behavior forced? I tried to change the error mode like:
$mysql = new \Pixie\QueryBuilder\QueryBuilderHandler($mysqlConnection);
$pdo = $mysql->pdo();
$pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_WARNING);
But it always trow exceptions. By some reason is ignoring my attribute change...
Thank you.
I am using pixie in kind of Specification pattern implementation, and I am faced that it supports NOT SQL operator only with IN
clause (whereNotIn
).
Is it possible to add NOT
operator support to any clause (whereIn
, whereNotIn
, orWhere
, etc)?
Thanks!
This is possible with event system, but there will be extra lag on events processing, so raw execution time should be calculated separately. Probably it is good candidate as third parameter of every after-*
method.
Everything is in the title (see http://php.net/manual/fr/pdo.exec.php ). Thanks for the great work :-)
Hello,
I was doing the following query:
$query = self::$qb->table(self::$defaultTbl)
->select("id")
->where("active", true);
$query->where(function($qb) use($lowLimit, $highLimit) {
$qb->whereBetween("field1", $lowLimit, $highLimit);
$qb->orWhereBetween("field2", $lowLimit, $highLimit);
});
However it fails saying there's an error on the SQL. I used ->getRawSql()
to debug this and got this:
SELECT `id` FROM `group` WHERE `active` = 1 AND (`field1` BETWEEN 0 AND 30OR `field2` BETWEEN 0 AND 30)
Apparently there's a space missing on the OR
here:
(`field1` BETWEEN 0 AND 30OR `field2`
Can someone point me where this issue might be? I tried to follow pixie's _where()
code but got lost.
Thank you.
Hello,
is it possible to specify a "where" like this :
QB::table('my_table')->where("
field> 35 OR
field2 != 'test' ");
I want to specify a where string in a form field.
Thank's for your answers and sorry for my english :)
Kaimite
I'd like to do something like:
QB::table('sometable')->where("date < datetime('now', '-600 seconds')")->get();
Hello,
I was doing this: $query->where("t.id", "IS", self::$mysql->raw("null"));
If I try to debug the query, I get this:
AND `t`.`id` IS null GROUP B...
However when I run the query, it gives me a PDO Exception with the following:
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''null' GROUP BY `S...
By inspecting MySQL's general_log
I found out, Pixie or PDO are actually trying to escape the "null" value with single quotes:
AND `t`.`id` IS 'null' GROUP ...
Why is this happening? Raw should not escape anything...
Thank you.
Hey,
Would it be possible to provide the ability to 'update or insert if not exist' ?
Something like: https://www.drupal.org/node/310085
Good morning,
I was trying the following:
$query = $db->table($groupTbl)
->select("id")
->where($db->raw("`" . $groupTbl . "`.dateCreated"), ">", $db->raw("'2015-04-20 15:00:00'"));
However it doesn't work properly as I get the following raw query:
SELECT `id` FROM `Funcionarios\Dados` WHERE `Data`.dateCreated
As you can see the second part of the query was omitted. It's missing > '2015-04-20 15:00:00'
. This issue seems to affect all where
queries like whereBetween
, orWhere
etc. Is there any specific reason for this inconsistency?
Thank you.
As addition to array, select could use seperated params : select('field1','field2','field3') or select('field1, field2, field3','another.joinfield')
If this is something pixie currently support I'd like to know
Otherwise i'd like to expand support to join on subqueries
for example
select * from table1 alias1
inner join (select id from table2 where field = 1) alias2 on alias2.id =alias1.id
i see the join param expects a string rather than an object so right now my ideal setup is not funcitonal:
$subquery = QB::table('table2')->select('name')->select('id)->where('field','=',1);
$query = QB::table('table1)->innerJoin(QB::table(QB::subQuery($subquery,'alias2')),'alias1.id','=','alias2.id');
I know the sql is a simple and dumb example I'm in a real world situation where we join on a subquery and I'd like to accomplish this without using raw
I want to specify aliases for columns, i. e. something like SELECT username AS name FROM users
.
Currently I can use raw statements to do that, but they are little bit unhandy. Is there better way to do this? If yes, probably need to add note to the documentation. Otherwise, please, implement it! It will be very helpful, especially on legacy databases.
Thanks.
Hello,
So I was tying to build this query:
SELECT * FROM `Store\Order\ProductStore`
LEFT JOIN `Store\Order\ProductStore` t ON
`Store\Order\ProductStore`.`product_id` = `t`.`product_id` AND
`Store\Order\ProductStore`.`id` < `t`.`id`
WHERE `Store\Order\ProductStore`.`product_id` IN (94) AND
`t`.`id` IS NOT null
GROUP BY `Store\Order\ProductStore`.`product_id`
I tried to go this way:
$query = QB::table($table);
$query->leftJoin(QB::raw("`" . $table . "` t"), function($table){
$table->on($table . ".product_id", "=", "t.product_id");
$table->on($table . ".id", "<", "t.id");
});
$query->whereIn($table . ".product_id", $input->ids);
$query->where("t.id", "IS", QB::raw("null"));
$query->groupBy($table . ".product_id");
However it doesn't work, I get the following erro stack:
{
"function": "Util\\{closure}",
"args": [
2,
"explode() expects parameter 2 to be string, array given",
"AppCore\/Include\/usmanhalalit\/pixie\/src\/Pixie\/QueryBuilder\/Adapters\/BaseAdapter.php",
455,
{
"value": []
}
]
},
{
"file": "AppCore\/Include\/usmanhalalit\/pixie\/src\/Pixie\/QueryBuilder\/Adapters\/BaseAdapter.php",
"line": 455,
"function": "explode",
"args": [
".",
[],
2
]
},
{
"file": "AppCore\/Include\/usmanhalalit\/pixie\/src\/Pixie\/QueryBuilder\/Adapters\/BaseAdapter.php",
"line": 327,
"function": "wrapSanitizer",
"class": "Pixie\\QueryBuilder\\Adapters\\BaseAdapter",
"object": {},
"type": "->",
"args": [
[]
]
},
{
"file": "AppCore\/Include\/usmanhalalit\/pixie\/src\/Pixie\/QueryBuilder\/Adapters\/BaseAdapter.php",
"line": 509,
"function": "arrayStr",
"class": "Pixie\\QueryBuilder\\Adapters\\BaseAdapter",
"object": {},
"type": "->",
"args": [
{
"\u0000*\u0000value": "`Store\\Order\\ProductStore` t",
"\u0000*\u0000bindings": []
},
""
]
},
Any tips on why this happening? If this is expected what's the way to do the table name alias with Pixie? Why isn't a raw query supported?
Thank you.
Hello,
is it possible to have the "query object" in after-insert event ?
I use after-insert / update / delete events to log each queries in a flat file.
If my sql server crash I can restore all queries done after my daily backup.
but in the after-select event I can't get query with :
$query = $queryObject -> getRawSql();
something like that :
QB::registerEvent('after-insert', ':any', function($queryBuilder, $insertId, $executionTime, $queryObject)
{
logQuery($queryObject -> getRawSql());
}
);
I tried this :
$queryObj = $queryBuilder -> getQuery();
$query = $queryObj -> getRawSql();
but it's not the good query :'(
Thank's !
It would be nice to be able to setup a class and constructor argument for the results when using the get()
method.
Something like:
public function asObject($class = '\\stdClass', $args = null)
{
$this->resultClass = $class;
$this->resultArgs = $args;
return $this;
}
I was wondering if it's possible to get the WHERE clause that makes up the statement from the query? Would like to be able to get what the WHERE clause that was used in the execution of the query.
Try this call but no results:
$query->getQuery('criteriaonly');
calling
$query->getQuery(); - Returns the entire SQL Query that is to get executed.
any possible way of accomplishing what is required?
max()
min()
and so.
Hi -
I'm having trouble with the orderBy method. When I want to order by more than one column I can't seem to pass the columns in an array as shown in your GitHub docs. I have a feeling I'm missing the boat here... Can anyone help me?
This doesn't work:
$query = QB::table('movies')
->select(array('fs_name','name','release_date', 'rating','video_format','imdb_stars'));
$query->orderBy(array( 'release_date', 'DESC', 'sort_name', 'ASC'));
$queryObj = $query->getQuery();
echo $queryObj->getRawSql();
$result = $query->get();
Errors:
Warning: explode() expects parameter 2 to be string, array given in .../vendor/usmanhalalit/pixie/src/Pixie/QueryBuilder/Adapters/BaseAdapter.php on line 360
Warning: Invalid argument supplied for foreach() in .../vendor/usmanhalalit/pixie/src/Pixie/QueryBuilder/Adapters/BaseAdapter.php on line 362
Warning: implode(): Invalid arguments passed in .../vendor/usmanhalalit/pixie/src/Pixie/QueryBuilder/Adapters/BaseAdapter.php on line 368
...
The raw SQL is:
SELECT `fs_name`, `name`, `release_date`, `rating`, `video_format`, `imdb_stars` FROM `movies` ORDER BY ASC
This works:
$query = QB::table('movies')
->select(array('fs_name','name','release_date', 'rating','video_format','imdb_stars'));
$query->orderBy('release_date', 'DESC');
$query->orderBy('sort_name', 'ASC');
$queryObj = $query->getQuery();
echo $queryObj->getRawSql();
$result = $query->get();
The raw SQL is:
SELECT `fs_name`, `name`, `release_date`, `rating`, `video_format`, `imdb_stars` FROM `movies` ORDER BY `release_date` DESC,`sort_name` ASC
Would it be possible to handle DateTime objects passed as conditions in where() ?
something like if ($value instanceof \DateTime) $value->format("Y-m-d H:i:s");
When I try to run a query with whereNot I get the following error,
Fatal error: Call to undefined method Pixie\QueryBuilder\QueryBuilderHandler::whereNot() in /home/ubuntu/workspace.....
The ->count
method doesn't work on SQLite. Need a stable way.
I'm attempting to find out if a table exists in MySQL using the following:
$query = $this->db->query("SHOW TABLES LIKE ?", [$table]);
However, I can't seem to get the results of that Query where I can tell the number of rows and if the table exists or not.
How should I go about this?
Hello,
I can't find a way to use the ->setFetchMode(\PDO::FETCH_ASSOC)
method with raw queries...
Is this possible? What about other queries?
Thank you.
When I use a join with a literal, in a multiple on statement, the literal is quoted with backticks:
->leftJoin($qb->raw('table2 AS tbl2'), function($table) use ($userId, $qb) {
//userid gets enclosed with backticks, changed to raw
$table->on('tbl2.UserId','=',$userId);
$table->on('tbl2.JoinId', '=', 'tbl1.JoinId');
})
Result:
... LEFT JOIN `tbl2`.`UserId` = `1234` AND `tbl2`.`JoinId` = `tbl1`.`JoinId`....
So I changed my join to use raw:
->leftJoin($qb->raw('table2 AS tbl2'), function($table) use ($userId, $qb) {
//userid gets enclosed with backticks, changed to raw
$table->on($qb->raw('tbl2.UserId = ?',$userId));
$table->on('tbl2.JoinId', '=', 'tbl1.JoinId');
})
Result:
... LEFT JOIN `tbl2`.`UserId` = ? `` AND `tbl2`.`JoinId` = `tbl1`.`JoinId`....
There are extra backticks left in the query.
Can i join one table several times?
Can i use alias like:
SELECT t1.* FROM table1 AS t1
INNER JOIN table2 AS t2 ON t1.id = t2.table1id
INNER JOIN table2 AS t22 ON t1.id = t22.table1id AND ...
Laravel has support for transactions via the
DB::transaction(function(){
DB::insert(...);
DB::delete(...);
etc.....
});
which auto begins and commits a transaction before and after calling the callback function.
Would you consider adding a method to support this?
Problem is with eventId, need a solution for that.
Hello,
Is it possible to get the table name in before-select event.
DB::registerEvent('before-select', ':any', function($queryBuilder)
{
$table = $queryBuilder -> getTableName();
$queryBuilder->setFetchMode(PDO::FETCH_CLASS, 'row' . usfirst($table));
}
);
I'm doing this with regex but there is maybe an easiest way !
Thank's
Kaimite
May be useful for caching, for example, when before-select
returns anything other than null
.
I am using a migration framework called LibMigrate. It expects me to return the SQL it should run. That is why I do not want to open yet another connection if that is not even required. So...what is the correct way to just obtain the SQL without conencting?
Kind regards, Ingwie.
driver = sqlite
None of this works:
QB::table('my_table')->where('updated', '<', 'date(\'now\')');
QB::table('my_table')->where('updated', '<', 'date("now")');
QB::table('my_table')->where('updated', '<', QB::raw('date(\'now\')'));
However, this works:
QB::query('select * from my_table where updated < date("now")');
I've tried to debug a bit and I see the date('now')
part it's going to PDO as it should be, without any sanitizing, so it seems like a PDO issue? Or am I doing something wrong?
Using function in select will throw error "Column not found: 1054 Unknown column". For example QB::table('sometable')->select('LEFT(somefield, 7) AS somealias')
Hello,
I was noticing this isn't possible, for e.g.:
$query = QB::table("example");
$query->select("id", "name");
$query->raw("where id != null");
$result = $query->get();
The line $query->raw("where id != null");
seems to be ignored.
Is there any specific reason why I can't insert a raw()
on the middle of the query? I guess it wasn't implement do the fluid interface, because we never know what parts of the query are set first...
Using the .*@dev composer version on a server running MariaDB, I cannot seem to get count to work.
Thankyou
Hello,
So I was thing to do the following query:
SELECT `orig_id` FROM `RPTC` WHERE `id` NOT IN (SELECT `id` FROM `PCT`)
My code was:
// Sub Query
$query2 = $db->table("PCT")->select("id");
// Main Query
$query = $db->table("RPTC")
->select("orig_id")
->whereNotIn("id", $db->subQuery($query2));
However I got this error message:
Catchable fatal error: Argument 2 passed to Pixie\QueryBuilder\QueryBuilderHandler::whereNotIn() must be of the type array, object given, called
Is there any specific reason why Pixie doesn't allow for a sub-query as the whereIn
/ whereNotIn
second parameter? How can this be accomplished? Apparently a simple where()
accepts sub-queries.
Thank you.
Hello,
I was trying to get records out of a table called CMS\Page
, yes there's a backslash on the table name. Unfortunately the backslash seems to break update queries...
I get a PDO exception like:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\\Page SET `id`='4',`title`='title1',`content`='test' at line 1"
This doesn't happen on select
queries.
By inspecting general_log
on MySQL server I found out that it was trying to run the following query:
UPDATE CMS\Page SET `id`='4',`title`='title1',`content`='content',`dateUpdated`='2015-03-17 00:32:05' WHERE `id` = '4'
As you can see, the table name, is not properly escaped as ``CMS\Page SET
like it should.
For instance on a select
query it escapes the table name properly:
SELECT `id`, `date`, `dateUpdated`, `title` FROM `CMS\Page` LIMIT 20
The same issue applied to delete queries.
Note: I'm not sure how this should be fixed, I tried to edit ->addTablePrefix()
method, however it seemed to do more harm than good.
Thank you.
It would be great to be able to set the default fetch mode in the query builder.
Not sure how to go about doing this but if it's something you might consider, I'd be happy to have go at something and submit a PR.
Hello,
I was tying to build a query with a whereIn() call including strings with backslashes...
$query->whereIn("name", ["\\One\\Two", "\\Antother\\One"]);
However by some reason the escaping seems to be failing and the query object raw SQL ends up as:
SELECT * FROM `Globals` WHERE `name` IN ('\One\Two', '\Antother\One');
And it doesn't work, If I go into mysql console and try the code generated by Pixie it won't find anything, however... if I add another slash..
SELECT * FROM `Globals` WHERE `name` IN ('\\One\\Two', '\\Antother\\One');
It finds the entries on my DB correctly. Any specific reason why Pixie doesn't place the double backslashes?
PS: On normal WHERE
queries this problem doesn't happen.
Thank you.
Hello,
There seems to be a behaviour mismatch between the way raw queries and pixie queries handle ->setFetchMode()
.
For instance, If I do:
$query = $db->table("page")
->where("title", "=", "test title")
->setFetchMode(\PDO::FETCH_ASSOC);
The returned array will give me an array as expected. Next time if I just issue:
$query = $db->table("page")
->where("title", "=", "test title");
Without specifying a fetch mode, it will default to PDO::FETCH_OBJ
as expected.
This is fine, however in raw queries, the fetch mode value is kept between two queries:
$query = $db->query("select * from page where title = 'test title'");
$query->setFetchMode(\PDO::FETCH_ASSOC);
Will return an array as expected. If I issue next, a query without specifying a fetch mode:
$query = $db->query("select * from page where title = 'test title'");
It will return an array as well. It will keep returning arrays until I manually set it to return objects again.
Why isn't the fetch mode changed to the default PDO::FETCH_OBJ
after $query->get()
similarly to what happens in non-raw queries?
Thank you.
Furthermore, currently if LIMIT
and OFFSET
are set, it provides 0 as number of rows, which is wrong.
Hello,
Is there any particular reason why at ->insert($data)
the $data
variable can't be an object for single inserts?
Or better yet, it could accept arrays of objects or an object with multiple objects inside to be inserted.
Thank you.
I understand now that I have to split my orderBys if I am specifying DESC. Thanks,
However, the array option in orderBy still does not seem to work:
$query = \QB::table('movies')
->select(array('fs_name','name','release_date', 'rating','video_format','imdb_stars'));
$query->orderBy(array( 'release_date', 'sort_name'));
$queryObj = $query->getQuery();
echo $queryObj->getRawSql();
$result = $query->get();
gives:
Warning: explode() expects parameter 2 to be string, array given in .../vendor/usmanhalalit/pixie/src/Pixie/QueryBuilder/Adapters/BaseAdapter.php on line 360
Warning: Invalid argument supplied for foreach() in .../vendor/usmanhalalit/pixie/src/Pixie/QueryBuilder/Adapters/BaseAdapter.php on line 362
Warning: implode(): Invalid arguments passed in .../vendor/usmanhalalit/pixie/src/Pixie/QueryBuilder/Adapters/BaseAdapter.php on line 368
...
raw SQL:
SELECT `fs_name`, `name`, `release_date`, `rating`, `video_format`, `imdb_stars` FROM `movies` ORDER BY ASC
My error or ???
Beau.
Please add information about column aliases to README.md
Would it be possible to add the ability to create (and, eventually, update) database schema?
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.