Reference in sub select instance

WININFO

Hi,
I have an sql query which has sub queries and in its sub queries there is in the selection condition a reference to the main query but it doesn't work, is there another way to do it?

<?php

/*
 * Example PHP implementation used for the index.html example
 */
// error_reporting(E_ALL);
// ini_set("display_errors", 1);
// Load Dolibarr environment
$res = 0;
// Try main.inc.php into web root known defined into CONTEXT_DOCUMENT_ROOT (not always defined)
if (!$res && !empty($_SERVER["CONTEXT_DOCUMENT_ROOT"])) $res = @include $_SERVER["CONTEXT_DOCUMENT_ROOT"]."/main.inc.php";
// Try main.inc.php into web root detected using web root calculated from SCRIPT_FILENAME
$tmp = empty($_SERVER['SCRIPT_FILENAME']) ? '' : $_SERVER['SCRIPT_FILENAME']; $tmp2 = realpath(__FILE__); $i = strlen($tmp) - 1; $j = strlen($tmp2) - 1;
while ($i > 0 && $j > 0 && isset($tmp[$i]) && isset($tmp2[$j]) && $tmp[$i] == $tmp2[$j]) { $i--; $j--; }
if (!$res && $i > 0 && file_exists(substr($tmp, 0, ($i + 1))."/main.inc.php")) $res = @include substr($tmp, 0, ($i + 1))."/main.inc.php";
if (!$res && $i > 0 && file_exists(dirname(substr($tmp, 0, ($i + 1)))."/main.inc.php")) $res = @include dirname(substr($tmp, 0, ($i + 1)))."/main.inc.php";
// Try main.inc.php using relative path
if (!$res && file_exists("../main.inc.php")) $res = @include "../main.inc.php";
if (!$res && file_exists("../../main.inc.php")) $res = @include "../../main.inc.php";
if (!$res && file_exists("../../../main.inc.php")) $res = @include "../../../main.inc.php";
if (!$res) die("Include of main fails");

// DataTables PHP library
require_once( "../lib/DataTables.php" );
// Alias Editor classes so they are easy to use

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

// Build our Editor instance and process the data coming from _POST
$editor = Editor::inst( $db, 'llx_wi_point_relais as pr', 'rowid')
    ->fields(
        Field::inst('pr.fk_soc', 'fk_soc')
            ->options( Options::inst()
                ->table( 'llx_societe as s, llx_societe_extrafields as se' )
                ->value( 's.rowid' )
                ->label( 's.nom' )
                ->where( function($q) {
                    $q ->where( function($r) {
                        $r ->where('se.point_relais', 1 );
                        $r ->where('s.rowid', 'se.fk_object', '=', false); //join
                    });
                } )
        ),
        Field::inst('soc.nom', 'point_relais'),
        Field::inst('pr.fk_product', 'fk_produit')
            ->options( Options::inst()
                ->table('llx_product as p')
                ->value('p.rowid')
                ->label('p.label')
        ),
        Field::inst('p.label', 'produit'),
        Field::inst('pr.qte_stock_init', 'stock_init') ,
        Field::inst( 'pr.fk_user_creat' )
            ->set( Field::SET_CREATE )
            ->setValue("$user->id"),
        Field::inst( 'pr.fk_user_modif' )
            ->set( Field::SET_EDIT )
            ->setValue("$user->id"),
        Field::inst("(SELECT count(*) FROM llx_commandedet as cd left join llx_commande as c on c.rowid = cd.fk_commande left join llx_commande_extrafields as ce on ce.fk_object = c.rowid left join llx_commandedet_extrafields as cde on cd.rowid = cde.fk_object where cde.fk_statut = 2 AND c.date_cloture is null)", 'stock_propre'),
        Field::inst("(SELECT count(*) FROM llx_commandedet as cd left join llx_commande as c on c.rowid = cd.fk_commande left join llx_commandedet_extrafields as cde on cd.rowid = cde.fk_object left join llx_commande_extrafields ce on ce.fk_object=cd.fk_commande where cde.fk_statut = 3 AND cd.fk_product=pr.fk_product AND ce.fk_point_relais=pr.fk_soc)", 'stock_clientele'),
        Field::inst("(SELECT count(*) FROM llx_commandedet as cd left join llx_commande as c on c.rowid = cd.fk_commande left join llx_commande_extrafields as ce on ce.fk_object = c.rowid left join llx_commandedet_extrafields as cde on cd.rowid = cde.fk_object where cde.fk_statut = 4 AND c.date_cloture is null AND cd.fk_product = pr.fk_product AND ce.fk_point_relais = pr.fk_soc)", 'stock_agence'),
        )
    ->leftJoin('llx_societe as soc','soc.rowid', '=', 'pr.fk_soc')
    ->leftJoin('llx_product as p','p.rowid', '=', 'pr.fk_product')
    
    ->process( $_POST )
    ->json();
    exit;

WININFO

Ok, my sub-select are too long

WININFO

How can i correct this ?

colin

Is this a duplicate of your other thread?

Colin

WININFO

yes I created the other question afterwards to differentiate
and I haven't found out where or how to close this one.