+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Feature Requests (https://forum.codeigniter.com/forumdisplay.php?fid=29)
+--- Thread: Add Exists Function To DB Helper (/showthread.php?tid=66261)
Add Exists Function To DB Helper - mertdogan - 09-29-2016
Sometimes we only want to know our criteria is there in the table. We don't want to count all of results.
In small tables it's not problem with use count_all_results() or ->get()->num_rows() but with big data this is really big performance problem.
If you add an "exists" function like "count_all_results", it will be a good choise.
You can select like these solutions:
SELECT TOP 1 * FROM TABLE INNER JOIN ANOTHERTABLE ON ....=.... WHERE ..... .....
or
SELECT EXISTS (SELECT * FROM ..... WHERE ......)
or
SELECT CASE
WHEN EXISTS (SELECT *
FROM TABLE
WHERE X <> 0
and Y = 22) THEN 1
ELSE 0
END AS NON_ZERO_EXISTS
...
...
like query results.
I think; it is simple for your current progress of file.
RE: Add Exists Function To DB Helper - salain - 09-29-2016
Oops!!, just realised this in for CI4 so it may not apply.
Not really sure what you are looking for.
But as I understand you only want to know if a value/record is found at lease once in the table
SELECT * FROM table where column1 = 'criteria1' AND column2 = 'criteria1' LIMIT 1;
Then just check if you have a record or not.
PHP Code:
$query = $this->db->get_where('mytable', array('col1' => 'val1' ,'clo2' => 'val2'), 1);
if($query){
echo 'found one maybe more';
}
else {
echo 'Nada';
}
RE: Add Exists Function To DB Helper - mertdogan - 10-09-2016
Thank you. This method really usefull.
Yes you understand what i looking for.
I'm using this query simply for search if exists:
if($this->db->select('TOP 1 *',false)->from('TABLE')->where(array('col1' => 'val1' ,'clo2' => 'val2'))->get()->num_rows()===1){
//EXISTS
}else{
//NOT EXISTS
}
I only want an extra function for these type problems.
Another note; if you don't use MSSQL SERVER versions that before 2012, there are some problems with LIMIT syntax. And it will be a performance problem.
RE: Add Exists Function To DB Helper - salain - 10-09-2016
Hi,
This may still not be appropriate for CI4, I am not sure as I have not used it yet.
However, I think this is too specific to be included in a framework.
But based on you first post you are concern about performance on large datasets.
As far are I know SQL, EXISTS is use with sub-queries and in most cases (if not all) a sub-query will require the server to create a temporary table in memory or on disk ( depends on server environment ).
If you use a Limit clause, the result is return after the limit is reach.
So in theory a limit clause would perform better
Index columns that are use for search will increase search performance, but will affect insert/update operations.
So you need to evaluate what is more taxing on the server.
It may also be a good idea to look into different SQL server.
Just my 2 cents.
RE: Add Exists Function To DB Helper - mertdogan - 10-10-2016
I don't think that it's too specific for framework because it's like "count_all_results" function. It can be counted simply with queries. But developer added this function for users may need. I think this the reason why EXISTS function may add.
RE: Add Exists Function To DB Helper - salain - 10-10-2016
Maybe it was not clear, It is just my opinion.
I maybe wrong, but if I take your example you only want to know if a value is found in the table, no matter how many time and you don't need the records. I don't see how you can write any sort of query that will out perform:
PHP Code:
$query = $this->db->get_where('mytable', array('col1' => 'val1' ,'clo2' => 'val2'), 1);
if($query){
echo 'found one maybe more';
}
else {
echo 'Nada';
}
You could add a select to limit it to only 1 field and if you use query rather than the query_builder could be a bit better on performance, but it would be minimal
An EXISTS function to suite your example and your need in this instance would pretty much do the same as this.
RE: Add Exists Function To DB Helper - mertdogan - 10-10-2016
Yes i accept i can do what i want by simple queries like yours or i wrote as example. I only recommended a function like "count_all_results" that it has a lot of simple query alternatives like this. For example:
$query = $this->db->get_where('mytable', array('col1' => 'val1' ,'clo2' => 'val2'));
$count_all_results=$query->num_rows();
And no need for "count_all_results()" function; with your thinking.