CODEWITHSUNDEEP
mysqlsql
William Falcon
William Falcon

Reputation: 9813

MYSQL view not ordering as expected

I have a table of cocktail recipes and other stuff. I have another table that shows the recipes that have been liked. I want to make a view of the mentioned recipes in the last day, and if that result is <1000, fill in the remaining 1000 with random recipes NOT on the FEED table.

Example

Feed: Recipe 1 liked today (1 min ago) (cocktail), Recipe 2 liked yesterday (not cocktail), recipe 3 liked today (1 hour ago)(cocktail), recipe 4 liked today (3 minutes ago) (not cocktail).

Recipe table: self explanatory

Category table:

recipe 1, cocktail
recipe 2, juice
recipe 3, cocktail
recipe 4 juice
recipe 3333 cocktail
recipe 4444 cocktail
recipe nnnn cocktail

My View needs to show:

Recipe 1, recipe 4,Recipe 3 (liked in most recent order). THEN to fill the rest of the 1000, get random from the recipe table: Recipe 4444, recipe 3333, recipe nnnn.

Final result: Recipe 1, recipe 4,Recipe 3, Recipe 4444, recipe 3333, recipe nnnn

The code below attempts to do this, but the order is wrong (the top doesn't have recipe 1, 4, 3 in that order. They are mixed around...

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `XXXX` 
    SQL XXXX
VIEW `cocktails` AS
    (select 
        `r`.`name` AS `name`,
        `r`.`myId` AS `myId`
    from
        ((`recipe` `r`
        join `feed` `f` ON ((`r`.`myId` = `f`.`recipe_id`)))
        join `category` `c` ON ((`r`.`myId` = `c`.`recipe_id`)))
    where
        (`c`.`name` like '%cocktails%')
    group by `r`.`name`
    order by (max(`f`.`timeStamp`) >= (now() - interval 1 day)) desc , (`r`.`myId` is not null) desc)

    union 

    (select 
        `r`.`name` AS `name`,
        `r`.`myId` AS `myId`
    from
        ((`recipe` `r`
        join `category` `c` ON (`r`.`myId` = `c`.`recipe_id`)))
    where
        (`c`.`name` like '%cocktails%')

    )
    limit 0,1000

Upvotes: 0

Views: 80

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

I think you can use an order by in a view in MySQL. However, I think you can solve your problem by combining the queries. Do a left outer join to the feeds table. Then, order the results by the presence of the feeds:

CREATE VIEW cocktails AS
    select r.name, r.myId
    from recipe r join
         category c
         ON r.myId = c.recipe_id left outer join
         feed f
         ON r.myId = f.recipe_id
    where c.name like '%cocktails%'
    group by r.name
    order by (f.recipe_id is not null) desc,
             max(f.timestamp) >= (now() - interval 1 day) desc,
             r.myId is not null desc
    limit 0,1000;

I also got rid of the back quotes -- they make the code quite hard to read.

Upvotes: 1

Related Questions