How to only get the users stuff from object?

sbjorklind

I'm trying to get back only what the user owns in an object. More specifically only the review that the user wrote.

The reviews object is inside of my summaries table. The information of every record in the summaries table is the same for everyone except the reviews. So think of it as maybe different dishes on a restaurant which is the same for everyone but different users have written different reviews for that same dish, and the user can only see their review. So I figured creating an object for the reviews with a table reference to my reviews table would be a good idea.

As you can see here the reviews are linked to a user.

Every user also has their reviews linked.

So I'm just wondering how you would make it so the user only sees their review from the reviews object.

I have been told that custom queries is the way to go but that only works with query all records and not when getting a single record right?

So I did a precondition here in the reviews table first to make sure the correct user is the only one to access their review and for some reason that doesn't work.

Here is a more detailed picture.

I did the same thing in the summaries table and that also doesn't work.

Thankful for answers!

Michael Udinski

Hey @sbjorklind - I would consider adjusting your database schema a little bit. Your reviews table has a reference to the user_id but also your user table has a table reference to the reviews_id. I would remove your reviews_id reference on the user object. Typically, you don't want to duplicate data as it's harder to keep track of and update things in more than one place. It can lead to compromising data integrity.

I'd have something like this:

Table: User

 User
   email
   password
   ...

Review
  Comment
  Rating
  user_id

With this set up, you can easily query the Reviews that belong to a specific user

Query All Records from Review

WHERE

db: review.user_id = input: user_id

(you can also use the auth ID on authenticated endpoints in place of user_id)

sbjorklind

Okay, but what about the other stuff?

Michael Udinski

Re-reading your post I see you have a summaries table in there a reviews object: Are you storing additional metadata than the reviews_id in there? How many reviews can go into a summary?

If you anticipate this list to grow very large it might make more sense to put the relationship on the reviews table.

This would enable you to use an Addon of the review table with two parameters: summary.id = reviews.summary_id and auth ID (if authenticated) = reviews.user_id

Which should return only the reviews of a summary that belong to the specific user

sbjorklind

Hi! Sorry for late response but I got sick..

Is that the relationship you meant?

If so, I also deleted the object completely in the summaries table.

I'm not sure what you meant with me adding a addon but I added this in the query all records for the reviews. Is that something I should've done in the summaries table instead and do a addon of the review there? Because now it doesn't work.

Then how would I do when I would pull a single summary and want the accosieted review the user owns? Because then I can't use query all records.

sbjorklind

As I said, a more detailed explanation would be nice:)

Michael Udinski

Hi @sbjorklind

That relationship looks great on your reviews table.

Your first expression in your Custom Query is referencing text strings. You'll want to make sure you're filtering the database (noted by the DB).

Addons are a Xano feature used to pull related data into the response of your queries.

I recommend you attend an upcoming office hours (held 2x/every Tuesdays, we can help answer your questions over live screen share -- I think things will make more sense with live help!)

sbjorklind

Hi! I solved it, now the user only gets their reviews. Now I'm just wondering how it would be when the user posts a review. How do I make it so when the user posts a review it gets put into the right summary (the summary the person is in).

As you can see by the latest post it knows which user it is but not which summary it is.

Right now it looks like this.

Thank you again for the help!

Michael Udinski

Hi @sbjorklind - you would first do a database look-up (Get Record) to the appropriate table with the relationship then take the corresponding id and map it into your summaries_id field.

sbjorklind

Hi, sorry if I'm bad at understanding what you mean but I don't quite get it.

Is it something here I should change because this is the get record. But then again it only needs to get the summary itself as the review is an addon.

So again I'm supposing it's something I have to do in the post request. Because it posts in the review table and then I want the review to get the right summaries id associated with it as I then make the review an addon of the summary. By right summaries id I mean the summary the user is inside.

So in the summaries_id it would add the summaries_id that the user is inside (posted inside). I don't know if that would be hard to make as there is only one post request and i don't know how it would know which summary the user is inside. Maybe you explained it but I didn't understand and if that's the case could you do it more detailed?

Thank you again!

Michael Udinski

@sbjorklind what signifies a review belongs to a summary record? You would look up the summary record based on this information, pull the ID from the return variable and use dot notation to then populate the table reference with the corresponding ID... This would all be in the same function stack but with multiple steps. Here' is a light-weight example:

https://youtu.be/vyyfS5uc1lE

sbjorklind

Okay, yeah I understand but that would mean you'd have to type in which summary you are in. As you go trough the summaries and pick a date and then write a review you want that review that you wrote to go into the right summary. Is the only way to sort of have the user write in the date themselves so that it easily can post into that date or is there a way to sort of have it automatically detect which summary the user is inside. Maybe that is something that the front end handles. I don't mind the user writing in the date themselves but as I said, is there another way? How would you do it?

Michael Udinski

Yes, you can automatically let Xano handle the date - (for example, the timestamp "now" will take the exact time and date it is at time of execution -- you can format a timestamp into a date field) and have Xano automatically figure out which the summary_id something belongs to... You just need to define those rules in the function stack: As long as you can articulate how a review record is related to a summary_id Xano can handle it for you.

sbjorklind

Hi! Everything went fine and seemed to work but suddenly today when I tested the post request I got this error.

I have no idea what happened beacuse it all seemed to work. When I first opened the request some of the inputs where suddenly not hidden anymore and a second “summaries_name" as a external input had been added. I don't know if it's a bug or anything but I fixed it back to as it was and now I just get this error. Please help.

Michael Udinski

it looks like you're using a Database Link input, which takes all the schema and their settings and uses them as inputs for your endpoint. So when you add additional schemas to your table they will show up in the DB Link inputs

sbjorklind

https://youtu.be/T-38IMCmOKI

As you can see here, changing what you said doesn't help. I also need the summaries_name in the database as the user must have a place where they can input the summaries date. I also still get the same error. “Text filter requires a scalar value" and it refers to the comment param so it must be someting wrong there right? The weird thing is that it all worked and then this just happened randomly.

sbjorklind

https://youtu.be/T-38IMCmOKI

As you can see in the video, what you said didn't work. I also need the summaries_name in the database as the user needs to be able to type in the date of the summary themselves for it to work. Also, I get the same error messege. “Text filter requires a scalar value” and it refers to the Comment param, so it must be something wrong there right? The weird thing is that it all worked fine until it didn't randomly.

sbjorklind

https://youtu.be/T-38IMCmOKI

As you can see in the video, what you said didn't work. I also need the summaries_name in the database as the user needs to be able to type in the date of the summary themselves for it to work. Also, I get the same error messege. “Text filter requires a scalar value” and it refers to the Comment param, so it must be something wrong there right? The weird thing is that it all worked fine until it didn't randomly.