CODEWITHSUNDEEP
mysqlsqlleft-join
user2145673
user2145673

Reputation: 363

Store and retrieve user favorites in mysql

I have an android app that pulls information from mysql db and displays it in a list. Each list item has a favorite button that upon click updates the db with the username and with the list item.

I can get all user favorites using relational and foreign key. What I am trying to achieve is when the user re-logs in to the App and scrolls for available items he can already see which items he marked as favorite without going to his favorites page.

So for example when you are scrolling in your facebook page you can already see which posts you liked.

How can I achieve that?

First table

|------------------|--------------|----|-------|
|      Column      |     Type     |Null|Default
|------------------|--------------|----|-------|
|    //**id**//    |   int(11)    | No |
|    car_vendor    |  varchar(20) | No |
|    car_model     |  varchar(20) | No |
|    car_petro     |  varchar(10) | No |
| car_transmition  |  varchar(10) | No |
|     car_hand     |   int(11)    | No |
|  car_spedometer  |  varchar(7)  | No | 
|  car_engine_vol  |  varchar(4)  | No |
|   car_category   |   int(11)    | No |
|  car_post_date   |     date     | No |

second table

|----------------|------------|----|
|     Column     |    Type    |Null| Default
|----------------|------------|----|
|   //**id**//   |   int(11)  | No |
|    username    | varchar(20)| No |
|  favorites_id  |   int(11)  | No | <<< foreign key points to ID on first table

Upvotes: 2

Views: 4498

Answers (1)

Gianmarco
Gianmarco

Reputation: 2552

You can ask the db for that information,

the moment you get the list of all products you can do a LEFT JOIN, that can be used to connect a product, if liked, to the user. You can do something like this.

SELECT CAR_VENDOR,
    A.CAR_MODEL,
    A.CAR_PETRO,
    A.CAR_TRANSMITION,
    A.CAR_HAND,
    A.CAR_SPEDOMETER,
    A.CAR_ENGINE_VOL,
    A.CAR_CATEGORY,
    A.CAR_POST_DATE,
    B.USERNAME
FROM TABLE_ONE AS A
LEFT JOIN TABLE_TWO AS B ON B.FAVORITES_ID = A.ID
AND B.USERNAME = "actual user username"

This query should do the work, I didn't test it but you can take as an idea where to start

Only the object with the preference should have the username field filled

EDIT ------------------------------------------------

Taking the case that the OP presented we have that:

  • We have a single table that is a list of objects (cars) with their specific parameters (engine size, hand, model, category etc)
  • We have a N to N table that can connect each product to one or more user and each user to one or more product
  • We probably have a table of people (the "username" column of table two, that I wish to call "LIKES" should be a foreign key connected to the primary key in PEOPLE table)

The behavior is simple, we want something social-like where a person can "upvote" a car, obviously we don't want multiple votes on the same car from the same person thus we have to disable the function in case he/she has already voted.

We take in example a normal page in which a person has already voted some products and want to see the complete list.

In this case we want to see:

  • The COMPLETE list of item, no matter what.
  • The items that have already got a vote from that particular user.

One way to handle this is connecting CARS table with LIKES table (I leave the anagraphic of user outside to simplify).

The way to do this is using a JOIN connecting the two tables on the id of the car.

This SQL command will return a set of line that are the combination of each line inside the CARS followed by the relative username.

This will lead to mutiple lines for the same CAR, one for each upvoting user, needing a business logic to handle the problem of showing the correct page.

A step further in SQL logic is to use a LEFT JOIN.

The LEFT JOIN will include ALL the record from the first table and will connect them with the matching line of the second one.

Thus joining CARS with LIKE on car-id AND asking for the username that we want, will return the complete list of cars (all record from left) connected to (and only to) the records that match the username related condition.

The example can be this:

|         CARS         |
|  ID  |     MODEL     |
|------+---------------|
|   1  +     FORD      |
|   2  +     OPEL      |
|   3  +    FERRARI    |
|   4  +      GM       |
|----------------------|

|        LIKES         |
|  USERNAME  |  CAR_ID |
|------------+---------|
|   KEVIN    |    3    |
|    PAUL    |    3    |
|------------+---------|

If I use a simple JOIN like this:

SELECT ID, MODEL, USERNAME
FROM CARS
JOIN LIKES ON CAR_ID = ID

It will return this:

|  ID  |   MODEL   |  USERNAME  |
|   3  |  FERRARI  |   KEVIN    |
|   3  |  FERRARI  |    PAUL    |

That is incomplete

If I use something like this:

SELECT ID, MODEL, USERNAME
FROM CARS
LEFT JOIN LIKES ON CAR_ID = ID

I will have this:

|  ID  |   MODEL   |  USERNAME  |
|   1  |   FORD    |    NULL    |
|   2  |   OPEL    |    NULL    |
|   3  |  FERRARI  |   KEVIN    |
|   3  |  FERRARI  |    PAUL    |
|   4  |    GM     |    NULL    |

That is still incorrect, even if better.

The double record for the FERRARI will be removed adding a condition to the join:

SELECT ID, MODEL, USERNAME
FROM CARS
LEFT JOIN LIKES ON CAR_ID = ID AND USERNAME = "KEVIN"

This will return:

|  ID  |   MODEL   |  USERNAME  |
|   1  |   FORD    |    NULL    |
|   2  |   OPEL    |    NULL    |
|   3  |  FERRARI  |   KEVIN    |
|   4  |    GM     |    NULL    |

That is the correct list.

Once I've got this list I need to show it to the user, checking

if USERNAME is not NULL then DISABLE LIKE BUTTON

(PSEUDOCODE)

I hope this is useful to somebody

Upvotes: 4

Related Questions