Advanced search in Gallery 3

DiegoS

Joined: 2012-01-16
Posts: 20

Posted: Tue, 2013-03-19 10:47

Hello,

I'd like to know how to make an advanced search in Gallery3.

An example:

I would like to search for a specific keyword just in photos and not in albums

Many thanks,

DiegoS

tempg

Joined: 2005-12-17
Posts: 1857

Posted: Tue, 2013-03-19 16:19

Back up everything you have and try changing line 65 of gallery > modules > search > helpers > search.php:
"WHERE MATCH({search_records}.`data`) AGAINST ('$q' IN BOOLEAN MODE) AND {items}.`type` == 'photo' " .

-----------------------

EDIT: Rather than change the actual search query, you could just change what the query shows to the user.
Back up everything and open gallery > module > search > views > search.html.php.
Add <? endif; ?> after line 38 and add <? if ($item->is_photo): ?> after line 25:

There are advantages/disadvantages to each method.

DiegoS

Joined: 2012-01-16
Posts: 20

Posted: Tue, 2013-03-19 16:59

Thanks tempg

I'm going to try it !

DiegoS

micks80

Joined: 2012-04-22
Posts: 71

Posted: Sat, 2013-03-23 05:09

tempg wrote:

Back up everything and open gallery > module > search > views > search.html.php.
Add <? endif; ?> after line 38 and add <? if ($item->is_photo): ?> after line 25:

We tried the suggestions but unfortunately the first one didn't worked and the second one still displays albums. And is it possible to sort the search in Ascending or Descending order based on creation/upload date?

Thanks,
Mick

tempg

Joined: 2005-12-17
Posts: 1857

Posted: Tue, 2013-03-26 00:35

I use the first one for a section of one of my Gallery installs and it works. The only difference is that I display everything that is NOT a photo ( != 'photo' ). Sure you placed it in the correct file? The line you're replacing should already be the exact same (but without the last part). Does the search work at all when you use the line? (If not, make sure you have everything--including the period at the end).

I'm not sure what else to advise about that.

---------

Yes, you can sort by whatever field you want to sort by, but if you change the sort order, the best (most exact matches) will no longer be at the top of the results. (It would be like Yahoo/Google/Bing/whatever sorting the results by something other than what you most likely were looking for.)

I tried to find a workaround that allows sorting and also honors the search score (ranking), but I didn't get much feedback and haven't had time to nosedive deeper into sql scoring right now: http://galleryproject.org/node/108989 If you have any ideas, I'm open.

micks80

Joined: 2012-04-22
Posts: 71

Posted: Thu, 2013-03-28 18:47

tempg wrote:

Does the search work at all when you use the line? (If not, make sure you have everything--including the period at the end).
.

No it breaks the site with this SQL error -

Database_Exception [ Database Error ]:
#1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '== 'photo' ORDER BY `score` DESC LIMIT 21 OFFSET 0' at line 1 [ SELECT SQL_CALC_FOUND_ROWS `s_items`.*, MATCH(`s_search_records`.`data`) AGAINST ('brad brad*') AS `score` FROM `s_items` JOIN `s_search_records` ON (`s_items`.`id` = `s_search_records`.`item_id`) WHERE MATCH(`s_search_records`.`data`) AGAINST ('brad brad*' IN BOOLEAN MODE) AND `s_items`.`type` == 'photo' ORDER BY `score` DESC LIMIT 21 OFFSET 0 ]

I even tried AND_WHERE query but that didn't worked either i.e. "AND_WHERE({items}.`type`,'=',`photo`) " .

Quote:

Yes, you can sort by whatever field you want to sort by, but if you change the sort order, the best (most exact matches) will no longer be at the top of the results. (It would be like Yahoo/Google/Bing/whatever sorting the results by something other than what you most likely were looking for.)

We don't care about score, just want to sort the photos by date DESC, can you please tell where to change the code?

Thanks,
Mick

tempg

Joined: 2005-12-17
Posts: 1857

Posted: Tue, 2013-04-02 14:02

@micks80: Don't know what to tell you. It works for me. Haven't heard back from OP so I'm not sure if it works for them. Could be a host of different things.

micks80 wrote:

We don't care about score, just want to sort the photos by date DESC, can you please tell where to change the code?

Add some variant of ORDER BY {items}.`created` DESC to the query to the search helper file.

EDIT: Actually, you're not adding an "order by"; you're replacing the one that's already there.

micks80

Joined: 2012-04-22
Posts: 71

Posted: Wed, 2013-04-03 02:43

Quote:

Add some variant of ORDER BY {items}.`created` DESC to the query to the search helper file.

EDIT: Actually, you're not adding an "order by"; you're replacing the one that's already there.

Thanks for your reply, the ORDER BY gets automatically appended. Can you post the full SQL query from helpers/search.php because our line 65(3.0.6) has a bracket, here's the full SQL query that we tried:

  return
      "SELECT SQL_CALC_FOUND_ROWS {items}.*, " .
      "  MATCH({search_records}.`data`) AGAINST ('$q') AS `score` " .
      "FROM {items} JOIN {search_records} ON ({items}.`id` = {search_records}.`item_id`) " .
      "WHERE MATCH({search_records}.`data`) AGAINST ('$q' IN BOOLEAN MODE) AND {items}.`type` == 'photo' " .
      $album_sql .
      (empty($where) ? "" : " AND " . join(" AND ", $where)) .
      $access_sql .
      " ";

tempg

Joined: 2005-12-17
Posts: 1857

Posted: Wed, 2013-04-03 15:15

micks80 wrote:

3.0.6

That's a super important detail.

I haven't updated my search module since a long way back (3.0.1 I think) because my versions are modified to do all kinds of randomness. I didn't realize it had changed a bit; don't remember seeing it mentioned. (oops) Shouldn't matter much though.

What do you mean "ORDER BY gets automatically appended"?

Also, if "here's the full SQL query that we tried," where's the order by?

------------

I can't really do any tests right now, but looking (briefly) at the newer search module, I'd try replacing line 45. (Be sure to keep the quotation marks and the period at the end of the line.)

    $query = self::_build_query_base($q, $album) .
      " ORDER BY {items}.`created` DESC" .
      " LIMIT $limit OFFSET " . (int)$offset;

------------

Also, in the earlier post where I said line 65, it's now line 79.

DiegoS Joined: 2012-01-16 Posts: 20	Posted: Tue, 2013-03-19 10:47
	Hello, I'd like to know how to make an advanced search in Gallery3. An example: I would like to search for a specific keyword just in photos and not in albums Many thanks, DiegoS
	XUser login Username: * Password: * Create new account Request new password
tempg Joined: 2005-12-17 Posts: 1857	Posted: Tue, 2013-03-19 16:19
	Back up everything you have and try changing line 65 of gallery > modules > search > helpers > search.php: "WHERE MATCH({search_records}.`data`) AGAINST ('$q' IN BOOLEAN MODE) AND {items}.`type` == 'photo' " . ----------------------- EDIT: Rather than change the actual search query, you could just change what the query shows to the user. Back up everything and open gallery > module > search > views > search.html.php. Add `<? endif; ?>` after line 38 and add `<? if ($item->is_photo): ?>` after line 25: There are advantages/disadvantages to each method.

DiegoS Joined: 2012-01-16 Posts: 20	Posted: Tue, 2013-03-19 16:59
	Thanks tempg I'm going to try it ! DiegoS

micks80 Joined: 2012-04-22 Posts: 71	Posted: Sat, 2013-03-23 05:09
	tempg wrote: Back up everything and open gallery > module > search > views > search.html.php. Add `<? endif; ?>` after line 38 and add `<? if ($item->is_photo): ?>` after line 25: We tried the suggestions but unfortunately the first one didn't worked and the second one still displays albums. And is it possible to sort the search in Ascending or Descending order based on creation/upload date? Thanks, Mick

tempg Joined: 2005-12-17 Posts: 1857	Posted: Tue, 2013-03-26 00:35
	I use the first one for a section of one of my Gallery installs and it works. The only difference is that I display everything that is NOT a photo ( != 'photo' ). Sure you placed it in the correct file? The line you're replacing should already be the exact same (but without the last part). Does the search work at all when you use the line? (If not, make sure you have everything--including the period at the end). I'm not sure what else to advise about that. --------- Yes, you can sort by whatever field you want to sort by, but if you change the sort order, the best (most exact matches) will no longer be at the top of the results. (It would be like Yahoo/Google/Bing/whatever sorting the results by something other than what you most likely were looking for.) I tried to find a workaround that allows sorting and also honors the search score (ranking), but I didn't get much feedback and haven't had time to nosedive deeper into sql scoring right now: http://galleryproject.org/node/108989 If you have any ideas, I'm open.

micks80 Joined: 2012-04-22 Posts: 71	Posted: Thu, 2013-03-28 18:47
	tempg wrote: Does the search work at all when you use the line? (If not, make sure you have everything--including the period at the end). . No it breaks the site with this SQL error - Database_Exception [ Database Error ]: #1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '== 'photo' ORDER BY `score` DESC LIMIT 21 OFFSET 0' at line 1 [ SELECT SQL_CALC_FOUND_ROWS `s_items`., MATCH(`s_search_records`.`data`) AGAINST ('brad brad') AS `score` FROM `s_items` JOIN `s_search_records` ON (`s_items`.`id` = `s_search_records`.`item_id`) WHERE MATCH(`s_search_records`.`data`) AGAINST ('brad brad*' IN BOOLEAN MODE) AND `s_items`.`type` == 'photo' ORDER BY `score` DESC LIMIT 21 OFFSET 0 ] I even tried AND_WHERE query but that didn't worked either i.e. "AND_WHERE({items}.`type`,'=',`photo`) " . Quote: Yes, you can sort by whatever field you want to sort by, but if you change the sort order, the best (most exact matches) will no longer be at the top of the results. (It would be like Yahoo/Google/Bing/whatever sorting the results by something other than what you most likely were looking for.) We don't care about score, just want to sort the photos by date DESC, can you please tell where to change the code? Thanks, Mick

tempg Joined: 2005-12-17 Posts: 1857	Posted: Tue, 2013-04-02 14:02
	@micks80: Don't know what to tell you. It works for me. Haven't heard back from OP so I'm not sure if it works for them. Could be a host of different things. micks80 wrote: We don't care about score, just want to sort the photos by date DESC, can you please tell where to change the code? Add some variant of ORDER BY {items}.`created` DESC to the query to the search helper file. EDIT: Actually, you're not adding an "order by"; you're replacing the one that's already there.

micks80 Joined: 2012-04-22 Posts: 71	Posted: Wed, 2013-04-03 02:43
	Quote: Add some variant of ORDER BY {items}.`created` DESC to the query to the search helper file. EDIT: Actually, you're not adding an "order by"; you're replacing the one that's already there. Thanks for your reply, the ORDER BY gets automatically appended. Can you post the full SQL query from helpers/search.php because our line 65(3.0.6) has a bracket, here's the full SQL query that we tried: return "SELECT SQL_CALC_FOUND_ROWS {items}.*, " . " MATCH({search_records}.`data`) AGAINST ('$q') AS `score` " . "FROM {items} JOIN {search_records} ON ({items}.`id` = {search_records}.`item_id`) " . "WHERE MATCH({search_records}.`data`) AGAINST ('$q' IN BOOLEAN MODE) AND {items}.`type` == 'photo' " . $album_sql . (empty($where) ? "" : " AND " . join(" AND ", $where)) . $access_sql . " ";

tempg Joined: 2005-12-17 Posts: 1857	Posted: Wed, 2013-04-03 15:15
	micks80 wrote: 3.0.6 That's a super important detail. I haven't updated my search module since a long way back (3.0.1 I think) because my versions are modified to do all kinds of randomness. I didn't realize it had changed a bit; don't remember seeing it mentioned. (oops) Shouldn't matter much though. What do you mean "ORDER BY gets automatically appended"? Also, if "here's the full SQL query that we tried," where's the order by? ------------ I can't really do any tests right now, but looking (briefly) at the newer search module, I'd try replacing line 45. (Be sure to keep the quotation marks and the period at the end of the line.) $query = self::_build_query_base($q, $album) . " ORDER BY {items}.`created` DESC" . " LIMIT $limit OFFSET " . (int)$offset; ------------ Also, in the earlier post where I said line 65, it's now line 79.

Advanced search in Gallery 3

XUser login