[sqlite] date functions not working? about pixie HOT 14 CLOSED

This is so because where does not support RAW statements for now. I need this feature too, and going to implement it right now.

from pixie.

@dragonattack after #41 be merged, you may use ->where(QB::raw('updated < date("now")')) syntax. If you need it right now, you may use features/raw-statement-in-where branch of my fork.

from pixie.

Thank you very much, @neoascetic !
I will try your fix right now.

from pixie.

@dragonattack Works perfectly fine for me(on SQLite).

$query = QB::table('my_table')    
    ->where('value', '<', QB::raw('date(\'now\')'));

What error do you get?

@neoascetic Raw is already supported on where, or am I missing something?

from pixie.

@usmanhalalit In my case I do not need value specifying. I am connecting to Sphinx search server which supports MySQL protocol. In order to use MATCH("text"), I need to build query like that: ->where(QB::raw('MATCH(?)', $text)). But now pixie requires value, otherwise it inserts NULL into the query. Raw statements currently do not support placeholders, but I need parameters to be properly escaped. That's why PR.

from pixie.

@usmanhalalit

CREATE TABLE "test" (
  "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
  "date" text NOT NULL
);

INSERT INTO "test" ("id", "date") VALUES (1, '2014-11-24');
INSERT INTO "test" ("id", "date") VALUES (2, '2015-11-24');

$query = QB::table('test')->where('date', '<', QB::raw('date(\'now\')'));
print_r($query->get());

Array
(
    [0] => stdClass Object
        (
            [id] => 1
            [date] => 2014-11-24
        )

    [1] => stdClass Object
        (
            [id] => 2
            [date] => 2015-11-24
        )

)

from pixie.

I don't get any errors, but as you can see the query selects all the rows instead just only first with the date in the past. If you do:

$query = QB::table('test')->where('date', '>', QB::raw('date(\'now\')'));
print_r($query->get());

there will be no rows at all in result.

@neoascetic I've applied your fix, but it seems it's not fixing it, at least I've tried on test examples above.

I have "usmanhalalit/pixie": "1.*@dev" in my composer.json.

from pixie.

First, your date column is not DATETIME. Second, SELECT DATE('now') returns NULL. If you want to get current datetime, use NOW() function. So everything OK with the generated query.

from pixie.

I've tried many combinations, but nothing worked as expected. But in the same time all the queries working in plain SQL form, i.e.: SELECT * FROM test WHERE date < date('now'). This gives me just first row.

Are we talking about SQLite? All of that now I'm trying with SQLite (stated in first post). There's no NOW() and date column types in SQLite.

from pixie.

Probably should be reopened. be9d555 not fixes this, in fact.
@dragonattack sorry, I didn't pay attention on the title.

from pixie.

Found it.

Merged PR from @neoascetic , please follow his instruction and use master branch.

from pixie.

@neoascetic
For now ->where(QB::raw('date < date("now")')) works, but reopened this so that we can get this working: ->where('date', '<', QB::raw('date("now")'))

from pixie.

I think problem is not related to Pixie - it generates a valid sqlite query.

from pixie.

It binds the parameters, whereas these raw params should be included directly in the query.

from pixie.