How do i get aliases value from custom sql?

EricD

Hello again,
I have this query and editor

SELECT cat, sex, time, 
    @rank := CASE
        WHEN @sex = sex AND @cat = cat THEN @rank + 1
        WHEN (@sex := sex) IS NOT NULL AND (@cat := cat) IS NOT NULL THEN 1
        END AS rnk
        FROM res_001, (SELECT @rank := NULL, @sex := NULL, @cat := NULL) AS x
        ORDER BY sex, cat, time

Editor::inst($db, 'res_001 AS r')
        ->fields(
                Field::inst('r.rnk')
        )
        ->process($_POST)
        ->json();

Im getting this error:

An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'r.rnk' in 'field list'

But if i use

$data = Editor::inst($db, 'res_001 AS r') ....
$data = $db->sql($rawquery)->fetchAll();
echo json_encode( array(
    'data' => $data
) );

{data: "@rank := CASE\n        WHEN @sex = sexo AND @cat = categoria THEN @rank + 1\n        WHEN (@sex := sexo) IS NOT NULL AND (@cat := categoria) IS NOT NULL THEN 1\n        END"},

Works fine.

Off course i tried

Field::inst('r.@rank := CASE\n        WHEN @sex = sexo AND @cat = categoria THEN @rank + 1\n        WHEN (@sex := sexo) IS NOT NULL AND (@cat := categoria) IS NOT NULL THEN 1\n        END')

or

Field::inst('r.@rank := CASE WHEN @sex = sexo AND @cat = categoria THEN @rank + 1 WHEN (@sex := sexo) IS NOT NULL AND (@cat := categoria) IS NOT NULL THEN END')

And i get

An SQL error occurred: 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 '@rank := CASE\n WHEN @sex = sexo AND @cat = categoria THEN @rank + 1\n ' at line 1

So how can i get aliases value pls?

Thanks

allan

Hi,

Yes, I'm afraid the Editor PHP libraries aren't quite sophisticated enough to understand as complex SQL as that! What you need to do is create a VIEW with that statement and then have Editor read from that - see this example.

Allan

EricD

Well, thanks

Below how to use with custom query and aliases (only view mode)

$query = "SELECT cat, sex, time, @rank := CASE
        WHEN @sex = sex AND @cat = cat THEN @rank + 1
        WHEN (@sex := sex) IS NOT NULL AND (@cat := cat) IS NOT NULL THEN 1
        END AS rnk
        FROM res_001, (SELECT @rank := NULL, @sex := NULL, @cat := NULL) AS x
        ORDER BY sex, cat, time"

$data = $db->sql($query)->fetchAll();
echo json_encode( array(
    'data' => $data
) );


columns: [
    {data: "rnk"}
]