Add condition in OR

[Lancelot]

Hello to all.

I'm writing a plugin that I should search user parameters even custom fields of OSCLASS like this:

Code: [Select]

Search::newInstance()->addConditions(sprintf("%st_item_meta.s_value_id LIKE '%%%s%%'", DB_TABLE_PREFIX, $params['sPattern']));
Can you tell me if there is a way of adding search conditions in OR instead of AND.

Thanks in advance

[teseo]

I have no time to check what would be the correct procedure, but if addConditions() produces an AND, this is a very cheap workaround (I mean in terms of processing time, I already know my solution is very cheap )

Search::newInstance()->addConditions(sprintf("1 = 1 || %st_item_meta.s_value_id LIKE '%%%s%%'", DB_TABLE_PREFIX, $params['sPattern']));

Regards

[Lancelot]

  Ahahahahahaha... will also be cheap but it is also very smart. And also a lot of fun.

Do you have a cheap solution also to achieve a distinct search?? 

Thanks

[teseo]

What's exactly your goal here? Do you want to modify an existing query or add a new one? Please elaborate.

Regards

[Lancelot]

Ok. First of all, forgive my bad English.

I would like to search all records that contain the words entered by the user, both in item or custom fields (added by me) in the table

Code: [Select]

DB_TABLE_PREFIX . "_t_item_meta"
To properly do this query, I think we need to use a UNION query, but it could also work a DISTINCT.

But I can not find the way to do things either with the only classes osclass. Any suggestions?

[teseo]

Do you want default searches being extended to meta table or is this intended as a separate feature?

Regards

[Lancelot]

extend the search similarly to one of the two situations that give roughly the same result:

Code: [Select]

SELECT oc_t_item.pk_i_id
FROM (oc_t_item)
LEFT  JOIN oc_t_item_description as d ON d.fk_i_item_id = oc_t_item.pk_i_id
WHERE MATCH(d.s_title, d.s_description) AGAINST('Milano' IN BOOLEAN MODE)
AND d.fk_c_locale_code LIKE 'it_IT'
AND oc_t_item.b_enabled = 1 AND oc_t_item.b_active = 1 AND oc_t_item.b_spam = 0 AND (oc_t_item.b_premium = 1 || oc_t_item.dt_expiration >= '2013-09-23 08:16:13')
UNION
(SELECT d.pk_i_id
FROM (oc_t_item as d, oc_t_item_meta as m)
LEFT JOIN oc_t_item_meta ON m.fk_i_item_id = d.pk_i_id
WHERE m.s_value LIKE '%Milano%');

OR

Code: [Select]

SELECT DISTINCT oc_t_item.pk_i_id
FROM (oc_t_item, oc_t_item_meta)
LEFT  JOIN oc_t_item_description as d ON d.fk_i_item_id = oc_t_item.pk_i_id
WHERE MATCH(d.s_title, d.s_description) AGAINST('Milano' IN BOOLEAN MODE)
AND d.fk_c_locale_code LIKE 'it_IT'
AND oc_t_item.b_enabled = 1 AND oc_t_item.b_active = 1 AND oc_t_item.b_spam = 0 AND (oc_t_item.b_premium = 1 || oc_t_item.dt_expiration >= '2013-09-23 08:16:13')
AND  oc_t_item_meta.fk_i_item_id = oc_t_item.pk_i_id AND 1 = 1 || oc_t_item_meta.s_value LIKE '%Milano%'
ORDER BY dt_pub_date desc
LIMIT 1000;
Unless I'm missing something, it seems that the class SEARCH does not provide this type of parameters.

Thanks for your attention.

[teseo]

What you need you may get most of it looking at these functions:

http://forums.osclass.org/general-help/sort-by-price-multiple-currency-issue/msg66219/#msg66219

Examine oc-includes\osclass\model\Search.php to know which functions are available.

Adding a UNION, I couldn't find it there... Modifying what is already built in the query, either... But I think you may use addField($fields) to add your DISTINCT provoking some redundance, but still it's cheap...

SELECT oc_t_item.pk_i_id [default], DISTINCT oc_t_item.pk_i_id as meta_included_id

Then you'd need to get rid in the results of $results['pk_i_id'] replacing it with $results['meta_included_id']

Hope this will helps you, regards

[Lancelot]

Forgive me but I did not understand.

DISTINCT must be the first keyword after SELECT. With the function addField () would not be in the correct position and the query would return an error. Is there something I'm missing?

I'd like to avoid changing the native classes of OSCLASS but it seems to me that there is no other solution. Yet I think that the search extended to include custom fields is a very useful thing.

Maybe it is a suggestion for a future release.

How do you see?

[teseo]

DISTINCT must be the first keyword after SELECT.

Sorry, you're quite right.

Well, helpers and hooks are there to make modifications easier, but if you have no other option, nobody forces you to stick to core procedures. Build your query from scratch inside your theme files replacing the default one. You could use the DAO model or even the old method "$conn = getConnection(); $result=$conn->osc_dbFetchResult("SELECT * FROM....", being an internal query where no user is involved and he couldn't tamper with it, I think that would be safe too.

Regards