S
Asked
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')
->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:
```php
$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
in code i can find for example:
```php
$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:
```php
$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
- EGAnswered@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. - SAnsweredThank 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 - EGAnswered**[Ste](/u/iojedi)** Please can you state clearly, the end result of what you want to achieve?
Secondly, Are you using Datatable to display your query result? - SAnsweredOk... 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!! - EGAnswered**[Ste](/u/iojedi)** 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. - SAnsweredHi 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 - EGAnswered**[Ste](/u/iojedi)** Chat me up on Skype and share your screen.
Skype ID: enyinnayag - MSAnsweredHello,
No. There is no option to run query with datatables. Datatables library only support active records to generate queries.
Thank you - SAnsweredok... but there is a way to use UNION and sub SELECT with Datatables library?
- EGAnswered**[Ste](/u/iojedi)** 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. - Login to Reply