Little Hint To Sql Query

Asked 5 years ago

Hi, i need a little hint please!

in code i can find for example:

 $this->datatables
                    ->select('image, code, name, wp.quantity, alert_quantity')
                    ->from('products')
                    ->join("( SELECT * from {$this->db->dbprefix('warehouses_products')} WHERE warehouse_id = {$warehouse_id}) wp", 'products.id=wp.product_id', 'left')
                    ->where('alert_quantity > wp.quantity', NULL)
                    ->or_where('wp.quantity', NULL)
                    ->where('track_quantity', 1)
                    ->group_by('products.id');
echo $this->datatables->generate();

If i’d like to use a full query like for example:

  $this->datatables
                    ->query("SELECT a.* FROM ... ORDER BY ...");
echo $this->datatables->generate();

But i think i use a wrong syntax… Can you help my with a hint? or what kind of framework i have to check?

Thank you in advance

EG
Enyinnaya Gift
Answered 5 years ago
@STE. I am not sure I understood your question here. Maybe you will need to be clear on what you want to achieve so that someone can easily guide you.

Meanwhile $this->datatables means that you are using CI Datatable libraries, to get values for your Datatable.

Datatable class has its various functions for generating the needed SQL Query. Example select, or_where, where, group_by, generate e.t.c to generate, build all the necessary portion/segments and performs the main query based on results set from chained statements.

If you’re looking at introducing a RAW SQL Query in between, consider using $this-db to refer it.
S
Ste
Answered 5 years ago
Thank you!

I like to use this query on datatables on getQuantityAlerts FUNCTION… there is no way to call a raw sql inside datatable class?

In my query i have SELECT, a sub SELECT with CONCAT, a LEFT JOIN and UNION… the query works well on DB with direct calling.

Thank you in advance
EG
Enyinnaya Gift
Answered 5 years ago
Ste Please can you state clearly, the end result of what you want to achieve?

Secondly, Are you using Datatable to display your query result?
S
Ste
Answered 5 years ago
Ok… sorry for my bad english!

i need to convert this:

$this->datatables ->select(‘image, code, name, wp.quantity, alert_quantity’) ->from(‘products’) ->join(“( SELECT * from {$this->db->dbprefix(‘warehouses_products’)} WHERE warehouse_id = {$warehouse_id}) wp”, ‘products.id=wp.product_id’, ‘left’) ->where(‘alert_quantity > wp.quantity’, NULL) ->or_where(‘wp.quantity’, NULL) ->where(‘track_quantity’, 1) ->group_by(‘products.id’); echo $this->datatables->generate();

into a raw SQL query.

I need this because in my custom sql query i have UNION and sub SELECT and i don’t think is possible to have in datatables class.

Thank you in advance!!

Enyinnaya Gift

Answered 5 years ago

Ste This is the RAW SQL Query.

	SELECT `image`, `code`, `name`, `wp`.`quantity`, `alert_quantity`
	FROM `sma_products`
	LEFT JOIN ( SELECT * from sma_warehouses_products WHERE warehouse_id = {$warehouse_id}) wp ON `sma_products`.`id`=`wp`.`product_id`
	WHERE `wp`.`qty_alert` > `wp`.`quantity`
	OR `wp`.`quantity` IS NULL
	AND `wp`.`track_qty` = 1
	GROUP BY `sma_products`.`id`

Remember, you will need to replace {$warehouse_id} with the warehouse id.

Also, if you are running the query inside the script/code, you might need to remove the sma_ prefix.

Hope this is what you’re looking for.

S
Ste
Answered 5 years ago
Hi thank you!

But what i need is a way to put inside datatables a custom raw sql query where i can use UNION and sub SELECT.

So i want to use a raw sql query instead of the above example with datatables…

There is a way to use $this->datatables with raw SQL query with UNION and sub SELECT inside???

thank you
EG
Enyinnaya Gift
Answered 5 years ago
Ste Chat me up on Skype and share your screen.

Skype ID: enyinnayag
MS
Mian Saleem
Answered 5 years ago
Hello,

No. There is no option to run query with datatables. Datatables library only support active records to generate queries.

Thank you
S
Ste
Answered 5 years ago
ok… but there is a way to use UNION and sub SELECT with Datatables library?

Enyinnaya Gift

Answered 5 years ago

Ste Hello,

You will need to loop through the query result $query->result(), using foreach (($query->result()) as $row){..}, convert $row to an array and select only the array values from it into an array $data, then associate it to aaData variable.

See an example below

        if ($query->num_rows() > 0) {
            
            foreach (($query->result()) as $row) {
                $data[] = array_values( (array) $row); //convert object $row to an array and select only array value
            }
        }

        $sOutput = [
            'sEcho' => 1, // Intiger value
            'iTotalRecords' => $query->num_rows(), // total records 
            'iTotalDisplayRecords' => 10,
            'aaData' => $data,
            'sColumns' => '',
        ];
        echo json_encode($sOutput);

Let me know if this works for you.

0	Votes
27	Answers
877	Views

Question: Little Hint To Sql Query

Little Hint To Sql Query

Hi, i need a little hint please! in code i can find for example: ```php $this->datatables ->select('image, code, name, wp.quantity, alert_quantity')