Custom (complex) queries through the Jelly_Builder

[SpadXIII]

As described by Banks, this is a new feature request: http://github.com/jonathangeiger/kohana-jelly/issues/#issue/54/comment/154351

Copy from other issue: With Sprig, you could use a query-object:

$query = DB::query(Database::SELECT, $sql);
$model->load($query, FALSE);

[banks]

This is one that may or may not make sense and I suggested it just for consideration. Hopefully it should be possible to do any query using builder. There would be difficulties in managing aliases and possibly other things in allowing this.

We'll think though.

[SpadXIII]

Perhaps something like this would be a possible solution?

Jelly::select_query($model, $sql);

Then again, aliases will be a pain to filter out though.

[jonathangeiger]

I'm interested in something like this:

Jelly::query($model, $sql, $type);

For when the query builder just doesn't cut it. Ideally we can ensure that, for example, you can write your own SELECT query and have the model use it properly. This should be too hard. The hard part, as everyone mentioned, is aliases. There are a few options:

1. Make the programmer manually alias them themselves.
2. Parse the SQL passed and try to alias them before executing the query.
3. Make the programmer use the database columns and alias them when they come back.

None of these are ideal solutions. Any thoughts?

[banks]

Solution 3 sounds like the best to me. I don't really like it because it is inconsistent with everything else in Jelly query builder so far but it seems the best way.

The only other middle ground could be to use something like what I suggested a while back but we threw out - use curly braces or something in the query to identify an alias so you can mix and match easily.

To be honest, any sort of string parsing and auto aliasing here takes us dangerously close to Doctrine - writing our own SQL dialect etc. That is somewhere I don't really want to go. I would say that writing your own queries is a real edge case and we shouldn't put a lot of effort into parsing SQL. GO with 3, queries should be written entirely as pure SQL with all database names, and we just run the through the right DB connection and return Jelly results.

[SpadXIII]

3 does indeed sound like the best solution, also when a 4th solution (before-execute string-parsing with curly braces) is added. This 4th solution would be almost identical to 2 though, with the addition of curly braces (which make parsing/aliasing easier)

So far I haven't had to use anything like this though. The query-builder has quite alot of power (unknown to me still, and more so when I added this feature request)

Right now, I think it's best to freeze this feature request and see if the need comes up in the near future. I'm about to start a big(gish) project soon for which I'd like to use Jelly. It should have some interesting queries later on.

[ccazette]

Can someone post an example of something not possible wiith Jelly Builder ? I agree with SpadXIII, may be it's best to hold to see if it is really needed, this goes a bit against the API logic. Though, somthing interesting here is that if this is implemented at some point, you could reuse the logic to use DB::expr() in Jelly Builder, something not really supported as of now I believe ?...

[banks]

I agree until someone has real-world problems that require this, it should probably be left.