Is it possible to "Query RAW in Editor" whit this SQL?

jgatica

I am using Datatable Editor, I see that to implement the crud, for example this "Simple inline editing" I need to make use of the Editor class, something like "Simple inline editing".

At the end of the post I wrote the questions.

        Editor::inst( $this->editorDb, 'datatables_demo' )
        ->fields(
            Field::inst( 'first_name' )->validator( 'Validate::notEmpty' ),
            Field::inst( 'last_name' )->validator( 'Validate::notEmpty' ),
            Field::inst( 'position' ),
            Field::inst( 'email' ),
            Field::inst( 'office' ),
            Field::inst( 'extn' ),
            Field::inst( 'age' )
            ->validator( 'Validate::numeric' )
            ->setFormatter( 'Format::ifEmpty', null ),
            Field::inst( 'salary' )
            ->validator( 'Validate::numeric' )
            ->setFormatter( 'Format::ifEmpty', null ),
            Field::inst( 'start_date' )
            ->validator( 'Validate::dateFormat', array(
                "format"  => Format::DATE_ISO_8601,
                "message" => "Please enter a date in the format yyyy-mm-dd"
            ) )
            ->getFormatter( 'Format::date_sql_to_format', Format::DATE_ISO_8601 )
            ->setFormatter( 'Format::date_format_to_sql', Format::DATE_ISO_8601 )
        )
        ->process( $post )
        ->json();    

But I need to "translate" this sql

SQL

SELECT A.ID_BODEGA,
       UPPER(LEFT(A.NOMBRE_BODEGA, 1)) + LOWER(SUBSTRING(A.NOMBRE_BODEGA, 2, LEN(A.NOMBRE_BODEGA))),
       B.ID_SECTION,
       UPPER(LEFT(B.NOMBRE, 1)) + LOWER(SUBSTRING(B.NOMBRE, 2, LEN(B.NOMBRE))),
       A.PROFUNDIDAD,
       COALESCE((
                    SELECT F.LINEAS
                    FROM SECTION_BODEGAS T,
                         CALENDARIO_SECTION_BODEGA F
                    WHERE T.ID_BODEGA = F.ID_BODEGA
                      AND T.SECTION = F.ID_SECTION
                      AND F.ID_SECTION = B.ID_SECTION
                      AND F.ID_BODEGA = A.ID_BODEGA
                      AND UPPER(F.DIA) = 'LUNES'
                      AND F.OPERATIVO = 1
                ), 0) LUNES,
       COALESCE((
                    SELECT F.LINEAS
                    FROM SECTION_BODEGAS T,
                         CALENDARIO_SECTION_BODEGA F
                    WHERE T.ID_BODEGA = F.ID_BODEGA
                      AND T.SECTION = F.ID_SECTION
                      AND F.ID_SECTION = B.ID_SECTION
                      AND F.ID_BODEGA = A.ID_BODEGA
                      AND UPPER(F.DIA) = 'MARTES'
                      AND F.OPERATIVO = 1
                ), 0) MARTES,
       COALESCE((
                    SELECT F.LINEAS
                    FROM SECTION_BODEGAS T,
                         CALENDARIO_SECTION_BODEGA F
                    WHERE T.ID_BODEGA = F.ID_BODEGA
                      AND T.SECTION = F.ID_SECTION
                      AND F.ID_SECTION = B.ID_SECTION
                      AND F.ID_BODEGA = A.ID_BODEGA
                      AND UPPER(F.DIA) = 'MIERCOLES'
                      AND F.OPERATIVO = 1
                ), 0) MIERCOLES,
       COALESCE((
                    SELECT F.LINEAS
                    FROM SECTION_BODEGAS T,
                         CALENDARIO_SECTION_BODEGA F
                    WHERE T.ID_BODEGA = F.ID_BODEGA
                      AND T.SECTION = F.ID_SECTION
                      AND F.ID_SECTION = B.ID_SECTION
                      AND F.ID_BODEGA = A.ID_BODEGA
                      AND UPPER(F.DIA) = 'JUEVES'
                      AND F.OPERATIVO = 1
                ), 0) JUEVES,
       COALESCE((
                    SELECT F.LINEAS
                    FROM SECTION_BODEGAS T,
                         CALENDARIO_SECTION_BODEGA F
                    WHERE T.ID_BODEGA = F.ID_BODEGA
                      AND T.SECTION = F.ID_SECTION
                      AND F.ID_SECTION = B.ID_SECTION
                      AND F.ID_BODEGA = A.ID_BODEGA
                      AND UPPER(F.DIA) = 'VIERNES'
                      AND F.OPERATIVO = 1
                ), 0) VIERNES,
       COALESCE((
                    SELECT F.LINEAS
                    FROM SECTION_BODEGAS T,
                         CALENDARIO_SECTION_BODEGA F
                    WHERE T.ID_BODEGA = F.ID_BODEGA
                      AND T.SECTION = F.ID_SECTION
                      AND F.ID_SECTION = B.ID_SECTION
                      AND F.ID_BODEGA = A.ID_BODEGA
                      AND UPPER(F.DIA) = 'SABADO'
                      AND F.OPERATIVO = 1
                ), 0) SABADO
FROM SECTION_BODEGAS A,
     PARAM_SECTION B
WHERE A.SECTION = B.ID_SECTION
ORDER BY A.ID_BODEGA, B.ID_SECTION ASC;

Questions
1. Does the "Editor ::" instance accept subqueries?
2. It is possible to edit in more than one table with this "Simple inline editing" implementation
3. Any advice that you could give me to carry out this?
4. The most important. Is it possible to "Query RAW in Editor" whit this SQL?

allan

1) No sorry.
2) Also no at the moment.
3) For anything more than a basic table you need to use a VIEW.
4) Not with our PHP libraries. That said, there is no reason why you couldn’t implement your own PHP code which does do this. The client / server protocol is fully documented, and I’m happy to answer any questions you have about it.

Allan