-1

I've been trying to get the following query working for days but I can't seem to solve my issue. I'm using the MySQL version 5.7.9

I have the following database:

CREATE TABLE user (
    id INT NOT NULL AUTO_INCREMENT,
    avatar VARCHAR(64) NOT NULL,
    prename VARCHAR(64) NOT NULL,
    name VARCHAR(64) NOT NULL,
    username VARCHAR(64) NOT NULL,
    password VARCHAR(256) NOT NULL,
    ts TIMESTAMP NOT NULL,
    PRIMARY KEY(id)
);

CREATE TABLE address (
    id INT NOT NULL AUTO_INCREMENT,
    street VARCHAR(128) NOT NULL,
    zip VARCHAR(10) NOT NULL,
    place VARCHAR(128) NOT NULL,
    PRIMARY KEY(id)
);

CREATE TABLE personUpload (
    id INT NOT NULL AUTO_INCREMENT,
    aid INT NOT NULL,
    name VARCHAR(128) NOT NULL,
    prename VARCHAR(128) NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(aid) REFERENCES address(id)
    ON DELETE RESTRICT ON UPDATE CASCADE
);
CREATE INDEX pAndName ON personUpload(name, prename);

CREATE TABLE imageUpload (
    id INT NOT NULL AUTO_INCREMENT,
    pid INT NOT NULL,
    sujet VARCHAR(128) NOT NULL,
    path VARCHAR(200) NOT NULL,
    takenOn DATE NOT NULL,
    takenAt VARCHAR(64) NOT NULL,
    deleted TINYINT NOT NULL,
    ts TIMESTAMP NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(pid) REFERENCES personUpload(id)
    ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE favorites (
    id INT AUTO_INCREMENT NOT NULL,
    imgid INT NOT NULL,
    uid INT NOT NULL,
    active TINYINT NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(imgid) REFERENCES imageUpload(id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY(uid) REFERENCES user(id)
    ON DELETE RESTRICT ON UPDATE CASCADE
);

Here is some sample data:

-- User table
INSERT INTO `user` VALUES (1,'/img/userpics/default.jpg','Hans','Meier','admin','$argon2i$v=19$m=1024,t=2,p=2$Qk1VbC9xc3BwM0ZSOW4xcw$vScQmts5pydI5xW18qQkiRHaxrdeDLNbBvFYXJ8bI8M','2019-11-11 09:31:29'),(2,'/img/userpics/b0749ece77da027125f201fa23e28244.gif','Huerden','Sohn','huerdensohn','$argon2i$v=19$m=1024,t=2,p=2$aXhSYzU1TnVnRklxNlNPaA$88G2zS5VqEDtom87ZCGqR2YlEhyJnLDZM0dDK72Qka4','2019-11-13 09:13:00'),(3,'/img/userpics/b8ce4c3e73696e9c4bc4b3933301e107.jpg','John','Doe','johndoe69','$argon2i$v=19$m=1024,t=2,p=2$QWFyN1hGRTFWQlJDMHdQQg$eZFY4L0rPMmVnK57kp3RZbkd5t2oFWOJ0goqlAc01Vs','2019-11-15 10:29:26');

-- Address table
INSERT INTO `address` VALUES (1,'Testavenue 6','1234','Townname'),(2,'Halloweg 1','4012','afdfdaafafd'),(3,'Halloweg 1','4012','dfaadfsafdadfs'),(4,'dfafdafds 5','4444','dfdsfafsdfas');

-- personUpload table
INSERT INTO `personupload` VALUES (1,1,'Parker','Peter'),(2,2,'Johnson','Joe'),(3,3,'Name','Prename'),(4,4,'fddfadafs','dfsfdsadfa');

-- imageUpload table
INSERT INTO `imageupload` VALUES (1,1,'JA','/img/upload/80s-retro-city-background-footage-072549572_prevstill.jpeg','2019-11-24','Place',0,'2019-11-15 10:33:18'),(2,1,'JA','/img/upload/909663.png','2019-11-24','Place',0,'2019-11-15 10:33:18'),(3,1,'JA','/img/upload/1486835476509.jpg','2019-11-24','Place',0,'2019-11-15 10:33:18'),(4,2,'fsxfsfgssgfsgf','/img/upload/1573627770324.jpg','2019-11-08','Place',0,'2019-11-18 11:22:16'),(5,2,'fsxfsfgssgfsgf','/img/upload/1573633928517.png','2019-11-08','Place',0,'2019-11-18 11:22:16'),(6,2,'fsxfsfgssgfsgf','/img/upload/1573551081995.gif','2019-11-08','Place',0,'2019-11-18 11:22:16'),(7,3,'adfdfdfa','/img/upload/1568367191324.png','2019-11-03','Place',0,'2019-11-18 11:22:32'),(8,3,'adfdfdfa','/img/upload/70362754_129736035063450_1737794598899548160_n.jpg','2019-11-03','Place',0,'2019-11-18 11:22:32'),(9,3,'adfdfdfa','/img/upload/ZomboMeme_31082019200224.jpg','2019-11-03','Place',0,'2019-11-18 11:22:32'),(10,4,'sfddfdfs','/img/upload/65043948_1807204376092277_6361774892492259328_n.jpg','2019-11-09','sfddfsdfsa',0,'2019-11-18 11:23:18'),(11,4,'sfddfdfs','/img/upload/1560196429338.jpg','2019-11-09','sfddfsdfsa',0,'2019-11-18 11:23:18'),(12,4,'sfddfdfs','/img/upload/1559793227717.jpg','2019-11-09','sfddfsdfsa',0,'2019-11-18 11:23:18');

-- favorites table
INSERT INTO `favorites` VALUES (1,1,1,1),(2,2,3,0),(3,11,3,1),(4,5,3,1),(5,12,1,1);

Now what I'm trying to do is get every image where the favorites.id is either null or the user.id of the user that's currently using the system. I've already tried the following query:

SELECT personUpload.name, personUpload.prename, imageUpload.sujet, imageUpload.path, imageUpload.id, favorites.imgid, favorites.active, favorites.uid, DATE_FORMAT(imageUpload.takenOn, '%d.%m.%Y') AS takenOn, imageUpload.takenAt, address.street, address.zip, address.place 
FROM favorites
RIGHT JOIN user ON favorites.uid = user.id
RIGHT JOIN imageUpload ON favorites.imgid = imageUpload.id
RIGHT JOIN personUpload ON imageUpload.pid = personUpload.id
RIGHT JOIN address ON personUpload.aid = address.id
WHERE favorites.uid IS NULL OR favorites.uid = 2;

but this query doesn't show the image if another user saved it as favorite.

The following query doesn't work either since it shows duplicate entries:

SELECT personUpload.name, personUpload.prename, imageUpload.sujet, imageUpload.path, imageUpload.id, favorites.imgid, favorites.active, favorites.uid, DATE_FORMAT(imageUpload.takenOn, '%d.%m.%Y') AS takenOn, imageUpload.takenAt, address.street, address.zip, address.place 
FROM imageUpload 
JOIN personUpload ON imageUpload.pid = personUpload.id 
JOIN address ON personUpload.aid = address.id 
LEFT JOIN favorites ON imageUpload.id = favorites.imgid;

I expect the output to display the favorites.uid as either null or if it's a favorite of the user his corresponding user id. Currently it won't appear in the result if it's the favorite of another user.

I hope I've formulated the thing I'm trying to do well enough, for someone to understand.

Edit: The following query seems to work thanks to @eagle275

SELECT personupload.name, 
       personupload.prename, 
       imageupload.sujet, 
       imageupload.path, 
       imageupload.id, 
       favorites.uid, 
       Date_format(imageupload.takenon, '%d.%m.%Y') AS takenOn, 
       imageupload.takenat, 
       address.street, 
       address.zip, 
       address.place 
FROM   imageupload 
       JOIN personupload 
         ON imageupload.pid = personupload.id 
       JOIN address 
         ON personupload.aid = address.id 
       LEFT JOIN favorites 
              ON imageupload.id = favorites.imgid 
                 AND favorites.uid = 2 
ORDER  BY imageupload.id DESC 
LIMIT  10 offset 0 
  • Please add some sample data and expected output. – James Nov 18 '19 at 12:11
  • "but this query doesn't show the image if another user saved it as favorite." - please display what connection your current user has to the image - if its a favorite of another user... Please fill in some data that explain your use case and what results you expect – eagle275 Nov 18 '19 at 12:47
  • @eagle275 The user has no connection to the image unless he's added the image to his favorites. – Laurent Hee Nov 18 '19 at 12:54
  • @LaurentHee You are welcome to answer your own question, but you must do so in an answer, not in the question. – Paul White 9 Nov 20 '19 at 10:49
0

Your second select query does what you want ... - deliver all imageupload entries - together with who took it and where / when - and who marked it favorite ... I suggest you put a proper alias for favorites.uid to make it more prominent

http://sqlfiddle.com/#!9/b04a03/4

SELECT 
  CASE 
     WHEN favorites.uid=1 THEN 1
     ELSE 0
  END AS `my_favorite`,
  personUpload.name, personUpload.prename, imageUpload.sujet, imageUpload.path, imageUpload.id, favorites.imgid, favorites.active, favorites.uid as favorite, DATE_FORMAT(imageUpload.takenOn, '%d.%m.%Y') AS takenOn, imageUpload.takenAt, address.street, address.zip, address.place 
FROM imageUpload 
JOIN personUpload ON imageUpload.pid = personUpload.id 
JOIN address ON personUpload.aid = address.id 
LEFT JOIN favorites ON imageUpload.id = favorites.imgid;

gives a column in front that carries a 1 if the image is a favorite of user - or 0 in the other case .. - as example the user 1 has 2 favorites marked

To adjust for the user - put the user_id in question in the case-when statement in place of the 1

|improve this answer|||||
  • I think I poorly described what I want. What I really want is to find all images and if they're a favorite of the logged in user display another icon next to them. – Laurent Hee Nov 18 '19 at 13:18
  • Changed query to reflect your recent comment – eagle275 Nov 18 '19 at 13:38
  • Thanks for the answer it solved my problem. Didn't know that CASE exists, thanks for teaching me something new. – Laurent Hee Nov 18 '19 at 14:15
  • I just checked again and it still doesn't seem to solve my problem since I'm still getting duplicate records from the query if multiple users mark the same image as favorite – Laurent Hee Nov 18 '19 at 17:15
  • Think I got it working now. Gonna edit my post with the working query. – Laurent Hee Nov 18 '19 at 17:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.