Forums

I have tried to use the search feature on one of my functions, however when I have got multiple lines listed the query that it runs has AND for every search term rather than OR which is needed to find the search term within the selected areas.
What is the correct way to set up search?
Regards,
Dave

Hi Dave,
Yes, this is how the default search works, if you need to use OR then you will need to use the where conditions and ignore the built in search, use the following syntax:

model.field1:{data:keywords}
OR
model.field2:{data:keywords}
OR
model.field3:{data:keywords}

Best regards

Thanks Max,
I have been able to get it to work for the first model, however I can't get it to work for the other models within the same database read element.
The table displays the data from the same models I am wanting to search through. In the database the first table has user id's and the second table has the names I can search using the user id but not using the users name I added in /LIKE: as well to rule that out.
Any ideas or is this out of scope?
Regards,
Dave

Hi Dave,
You can include any model in the conditions:

Model1.field
OR
Model2.field

Is this what you are looking to do ?
Best regards

Hi Max,
That's the syntax that I am using but the results that it is returning is the same as not entering anything into the search box. I can see the query is being run in the debug as shown below.
If I put a search term in which is in the JobsRead model (ie a number) it will return the results that I expect but doesn't work when using a name.

Array
(
    [read_jobs_data] => Array
        (
            [log] => Array
                (
                    [0] => SELECT COUNT(`JobsRead`.`id_job`) AS `JobsRead.count` FROM `#__aa_job_data` AS `JobsRead` LEFT JOIN `#__comprofiler` AS `Rider1L` ON `JobsRead`.`r1_name` = `Rider1L`.`id` LEFT JOIN `#__comprofiler` AS `Rider2L` ON `JobsRead`.`r2_name` = `Rider2L`.`id` LEFT JOIN `#__comprofiler` AS `Rider3L` ON `JobsRead`.`r3_name` = `Rider3L`.`id` LEFT JOIN `#__aa_jobs_add_organisation` AS `collectfrom` ON `JobsRead`.`collect_name` = `collectfrom`.`id_org` LEFT JOIN `#__aa_jobs_add_organisation` AS `deliverto` ON `JobsRead`.`deliver_name` = `deliverto`.`id_org` WHERE `JobsRead`.`status` = 'neil' OR `JobsRead`.`status` = 'neil' OR `JobsRead`.`id_job` = 'neil' OR `JobsRead`.`collect_from` = 'neil' OR `JobsRead`.`deliver_to` = 'neil' OR `JobsRead`.`deliver_name` = 'neil' OR `JobsRead`.`collect_name` = 'neil' OR `JobsRead`.`date_time` = 'neil' OR `JobsRead`.`r1_name` = 'neil' OR `JobsRead`.`r2_name` = 'neil' OR `JobsRead`.`r3_name` = 'neil' OR `Rider1L`.`id` = 'neil' OR `Rider1L`.`user_id` = 'neil%' OR `Rider1L`.`cb_wholename` LIKE 'neil' OR `Rider2L`.`id` = 'neil' OR `Rider2L`.`user_id` = 'neil' OR `Rider2L`.`cb_wholename` = 'neil' OR `Rider3L`.`id` = 'neil' OR `Rider3L`.`user_id` = 'neil' OR `Rider3L`.`cb_wholename` = 'neil' OR `collectfrom`.`short_name` = 'neil' OR `deliverto`.`short_name` = 'neil';
                    [1] => SELECT `JobsRead`.`status` AS `JobsRead.status`, `JobsRead`.`id_job` AS `JobsRead.id_job`, `JobsRead`.`collect_from` AS `JobsRead.collect_from`, `JobsRead`.`deliver_to` AS `JobsRead.deliver_to`, `JobsRead`.`deliver_name` AS `JobsRead.deliver_name`, `JobsRead`.`collect_name` AS `JobsRead.collect_name`, `JobsRead`.`date_time` AS `JobsRead.date_time`, `JobsRead`.`r1_name` AS `JobsRead.r1_name`, `JobsRead`.`r2_name` AS `JobsRead.r2_name`, `JobsRead`.`r3_name` AS `JobsRead.r3_name`, `Rider1L`.`id` AS `Rider1L.id`, `Rider1L`.`user_id` AS `Rider1L.user_id`, `Rider1L`.`cb_wholename` AS `Rider1L.cb_wholename`, `Rider2L`.`id` AS `Rider2L.id`, `Rider2L`.`user_id` AS `Rider2L.user_id`, `Rider2L`.`cb_wholename` AS `Rider2L.cb_wholename`, `Rider3L`.`id` AS `Rider3L.id`, `Rider3L`.`user_id` AS `Rider3L.user_id`, `Rider3L`.`cb_wholename` AS `Rider3L.cb_wholename`, `collectfrom`.`short_name` AS `collectfrom.short_name`, `deliverto`.`short_name` AS `deliverto.short_name` FROM `#__aa_job_data` AS `JobsRead` LEFT JOIN `#__comprofiler` AS `Rider1L` ON `JobsRead`.`r1_name` = `Rider1L`.`id` LEFT JOIN `#__comprofiler` AS `Rider2L` ON `JobsRead`.`r2_name` = `Rider2L`.`id` LEFT JOIN `#__comprofiler` AS `Rider3L` ON `JobsRead`.`r3_name` = `Rider3L`.`id` LEFT JOIN `#__aa_jobs_add_organisation` AS `collectfrom` ON `JobsRead`.`collect_name` = `collectfrom`.`id_org` LEFT JOIN `#__aa_jobs_add_organisation` AS `deliverto` ON `JobsRead`.`deliver_name` = `deliverto`.`id_org` WHERE `JobsRead`.`status` = 'neil' OR `JobsRead`.`status` = 'neil' OR `JobsRead`.`id_job` = 'neil' OR `JobsRead`.`collect_from` = 'neil' OR `JobsRead`.`deliver_to` = 'neil' OR `JobsRead`.`deliver_name` = 'neil' OR `JobsRead`.`collect_name` = 'neil' OR `JobsRead`.`date_time` = 'neil' OR `JobsRead`.`r1_name` = 'neil' OR `JobsRead`.`r2_name` = 'neil' OR `JobsRead`.`r3_name` = 'neil' OR `Rider1L`.`id` = 'neil' OR `Rider1L`.`user_id` = 'neil%' OR `Rider1L`.`cb_wholename` LIKE 'neil' OR `Rider2L`.`id` = 'neil' OR `Rider2L`.`user_id` = 'neil' OR `Rider2L`.`cb_wholename` = 'neil' OR `Rider3L`.`id` = 'neil' OR `Rider3L`.`user_id` = 'neil' OR `Rider3L`.`cb_wholename` = 'neil' OR `collectfrom`.`short_name` = 'neil' OR `deliverto`.`short_name` = 'neil' ORDER BY `JobsRead.date_time` DESC LIMIT 30;
                )
            [var] => Array
                (
                    [0] => Array
                        (
                            [JobsRead] => Array
                                (
                                    [status] => 1
                                    [id_job] => 1252514
                                    [collect_from] => 8
                                    [deliver_to] => 1
                                    [deliver_name] => 2
                                    [collect_name] => 52
                                    [date_time] => 2018-02-06 08:04:01
                                    [r1_name] => 0
                                    [r2_name] => 0
                                    [r3_name] => 0
                                )
                            [Rider1L] => Array
                                (
                                )
                            [Rider2L] => Array
                                (
                                )
                            [Rider3L] => Array
                                (
                                )
                            [collectfrom] => Array
                                (
                                    [short_name] => Surgery
                                )
                            [deliverto] => Array
                                (
                                    [short_name] => RD
                                )
                        )
                    [1] => Array
                        (
                            [JobsRead] => Array
                                (
                                    [status] => 0
                                    [id_job] => 1252513
                                    [collect_from] => 1
                                    [deliver_to] => 2
                                    [deliver_name] => 42
                                    [collect_name] => 16
                                    [date_time] => 2018-02-06 08:03:01
                                    [r1_name] => 505
                                    [r2_name] => 0
                                    [r3_name] => 0
                                )
                            [Rider1L] => Array
                                (
                                    [id] => 505
                                    [user_id] => 505
                                    [cb_wholename] => Nick
                                )
                            [Rider2L] => Array
                                (
                                )
                            [Rider3L] => Array
                                (
                                )
                            [collectfrom] => Array
                                (
                                    [short_name] => Hospital
                                )
                            [deliverto] => Array
                                (
                                    [short_name] => DS
                                )
                        )

Hi Twincarb,
​
Please try to test the SQL in phpmyadmin, does it return the results you expect there ?
​
You can also try to use LIKE because if the record value does not match the search string exactly then no results will be returned.
​
Best regards

Hi Max,
​
I tried the query in phpmyadmin and it gave the same result as in CC. I took another look at what I was telling it to do, I was literally telling it to go around in circles! left join on a left join on the same table . Which is fine in producing the table and extracting the data however when its tied into a search query it didn't work at all, I am looking at how I can rework the logic on the sub-models using "multiple matching records"
Regards,
​
Dave