Joins & Sub-Selects

Server Code Codeless
1

Hi
I’m building a recommendation-app with an algorithm that calculates a score to rank the items by. This score is individual for every user.
In my old php-version of the app, I calculated this algorithm-score by querying the item-table and in the SQL adding a custom column that is based on a 4-level-nested-join-sub-select.
What would be the best way to get a similar solution in backendless codeless? I don’t think there are no options for calculated select-fields or sub-selects, right?

here some example code

SELECT *, score.score AS score
FROM items 
LEFT JOIN (SELECT * FROM votes … ) AS user_vote ON …
LEFT JOIN (
  SELECT score.item_id, SUM(alikes.calcu * score.vote ) AS score
  FROM votes AS score
  LEFT JOIN (
    SELECT v1.user_id AS user, v2.user_id AS alike, sum( (abs( v1.vote - v2.vote ) ) ) AS calcu ,@rownum := @rownum + 1 AS rank
    FROM votes AS v1
    LEFT JOIN votes AS v2 ON v1.post_id = v2.post_id
    JOIN (
		SELECT @rownum := 0
		) AS rank
    WHERE …
2

Hello @Jan_Gruchow

To unambiguously answer your question and find the best solution, could you please create a simple tables model/schema with relations and describe your algorithm with it?

Regards,
Viktor

3

Hi
The table-schema is extremely simple:

  • User-Table: objectId, email, password etc
  • Items-Table: objectId, title, desc, image etc
  • VotesTable: user-relation, item-relation, vote (1-5)

Think of it as a regular book-database where user can do a star-rating of these books.
The algorithm ranks (orders) the books (that have not been voted by the current user) based on a score that tries to calculate the likelihood of a user will like the book. For this I’m comparing the existing votes of the current user with the votes of all other users.

4

Do you have a description of the actual steps of the algorithm? I understand what it does, but I don’t understand how it does it.