0

I have two tables table_entries and table_images.

table_images holds a series of images for any single item in table_entries

both tables are organized by a vid which is essentially an item id.

Not all items in table_entries will have an image, while others may have multiple images

What I am trying to construct is a query to only select items from table_entries that have an image, specifically 10 entries that have an image, which i've built as follows:

SELECT * FROM table_entries 
INNER JOIN table_images ON (table_entries.vid = table_images.vid)
WHERE (model LIKE '%apple%' OR ext LIKE '%apple%') 
ORDER BY lastupdated DESC LIMIT 0,10

EDIT: lastupdated, model and ext all belong to table_entries, in fact all sorting, selecting, etc.. is done based on table_entries

The problem with the above query is that it is successfully only picking items with images, but if a single item has 10 images, then it will return the 10 rows as item #1 with each of its images as individual rows. The intention is to have 10 distinct items, and limit the join to only 1 image for each item from table_entries

So I want to somehow limit the join to 1 row for the entire join.

I've searched for the answer here on SO and found so many good answers to this question (MySQL INNER JOIN select only one row from second table, MySQL JOIN with LIMIT 1 on joined table) , however they all use aliases for selecting the tables.

I could use alias and fix my query, but there is a TON of PHP code that I would have to change to deal with the aliased table names.

Is it possible to fix my query to only select 1 row from the joined table without the use of aliases?

Mark
  • 3,653
  • 10
  • 30
  • 62
  • Do you want the last 10 **updated** items that have at least one image? Which table does the column `lastupdated` belong to? What about `model` and `ext`? – The Impaler Apr 17 '22 at 16:49
  • @TheImpaler Correct, the last 10 items in the `table_entries` table. In actuality I use a pagination system so my LIMIT might be 0,10, or 20,10, etc... `lastupdated`, `model`, `ext` belong to `table_entries` – Mark Apr 17 '22 at 16:51
  • Please specify to which tables the columns (lastupdated, model, ext) belong to. It's not clear in the question, – The Impaler Apr 17 '22 at 16:52
  • The query will be quite slow due to the use of LIKE for filtering. Also what's the problem of using table aliases? Maybe you are considering other type of aliases... Never been a problem afaik. – The Impaler Apr 17 '22 at 17:04
  • 2
    Please provide an example of the desired results vs the current undesirable results. *"there is a TON of PHP code that I would have to change to deal with the aliased table names"* Not following how this is an issue in PHP, unless you are dynamically building the query with the table names. Maybe provide an example as to how this is an issue to avoid other approaches that may conflict. Otherwise you can generally just use the actual table name as the subquery alias. – Will B. Apr 17 '22 at 17:35

1 Answers1

1

You can do:

select *
from (
  select *,
    row_number() over(partition by i.vid order by i.updated) as rn
  from table_entries e
  join table_images i on i.vid = e.vid
  where e.model like '%apple%' or e.ext like '%apple'
) x
where rn = 1 -- this is the key filter
order by lastupdated desc
limit 0, 10

Please consider this query could be awfully slow due to the use of LIKE '%text%' in the search condition, specially if the table table_entries has millions of rows.

The Impaler
  • 45,731
  • 9
  • 39
  • 76
  • You are correct, for some reason my original query is very fast (great server) but this one is amazingly slow. I will work on this basis and see if it can become faster – Mark Apr 17 '22 at 17:30
  • @Mark Seems like you are searching by keywords. If that's the case MySQL includes "full text search"; maybe it could be an option in your case, to speed up searches. – The Impaler Apr 17 '22 at 18:40
  • Correct! Yes I plan to go full text down the line. Many of my searches need <3 word length, which requires a setting change and full mysql restart which I cant do for the time being, so this kind of keyword search will have to do until I can pull off the full restart – Mark Apr 17 '22 at 18:56