usmanhalalit / pixie Goto Github PK

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.

I'd like to do something like:

QB::table('sometable')->where("date < datetime('now', '-600 seconds')")->get();

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?

As addition to array, select could use seperated params : select('field1','field2','field3') or select('field1, field2, field3','another.joinfield')

Everything is in the title (see http://php.net/manual/fr/pdo.exec.php ). Thanks for the great work :-)

Furthermore, currently if LIMIT and OFFSET are set, it provides 0 as number of rows, which is wrong.

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.

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

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.

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");

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;
}

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.

hi, @usmanhalalit
Whether to support the transaction?

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?

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.

Please add information about column aliases to README.md

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.

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.

Hey,
Would it be possible to provide the ability to 'update or insert if not exist' ?
Something like: https://www.drupal.org/node/310085

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.

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?

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!

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.

Would it be possible to add the ability to create (and, eventually, update) database schema?

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.

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.

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

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.

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.

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.

Hello,

is it possible to specify a "where" like this :

QB::table('my_table')->where("field> 35 ORfield2 != 'test' ");

I want to specify a where string in a form field.

Thank's for your answers and sorry for my english :)

Kaimite

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 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?

Using function in select will throw error "Column not found: 1054 Unknown column". For example QB::table('sometable')->select('LEFT(somefield, 7) AS somealias')

max()
min()
and so.

May be useful for caching, for example, when before-select returns anything other than null.

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 !

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...

The ->count method doesn't work on SQLite. Need a stable way.

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.....

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.

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.

Using the .*@dev composer version on a server running MariaDB, I cannot seem to get count to work.

Thankyou

Problem is with eventId, need a solution for that.