CODEWITHSUNDEEP
c#asp.net.netasp.net-core
chumberjosh
chumberjosh

Reputation: 516

Query database for items using different Ids for the same object

So I'm trying to get all the 'UserPhotos' where their 'userId' == 'FollowerId', I get a problem when there is more than one 'FollowerId', I need to know how to make it work for a list of 'followerIds' so users can get back the 'UserPhotos' of all of the 'Users' they are following

    [HttpGet("u/{username}")]
    public async Task<IQueryable> GetSpecificFeed(string username)
    {
        var user = _context.Users.FirstOrDefault(x => x.Username == username);
        // Follower is the user that is being followed
        // Following is the user that is doing the following (so the current logged in user)

        var userId = user.Id;
        // This bit works and returns the correct userId

        var followerIds = _context.Follows.Where(x => x.FollowerId == userId);
        // This should return all of the 'FollowerIds' that the current user is following
        // This however doesn't do that, it just throws this error:
        // SQLite Error 1: 'no such column: x.UserId' (I don't know why)

        var feed = _context.UserPhotos.Where(x => x.UserId == userId);
        // This currently returns all of the 'UserPhotos' if their 'UserId' is equal to the 'UserId' 
        // (which is the id of the user that is logged in) -> This isn't what I want it to do
        // Once the 'followerIds' return the correct thing, it should then get the userPhotos where
        // The UserId == followerIds (followerIds will be an array, so it will have to check through
        // multiple values to get the whole 'feed'

        return feed;
    }

So this is what I cant figure out

  1. How to get the FollowerIds (as an array (I think they need to be an array but I'm not sure))
  2. How to get the UserPhotos when the 'userId' has multiple values

(p.s. I'm not sure if the title is good, let me know if I should change it)

[EDIT]

This is the follows class

 public class Follow
    {
        public int Id { get; set; }

        public int FollowingId { get; set; }
        // user that is following

        public int FollowerId { get; set; }
        // user that is being followed
    }

User class (I removed properties that aren't relevant in this example)

public class User
    {
        public int Id { get; set; }
        public string Username { get; set; }
        public ICollection<UserPhoto> UserPhotos { get; set; }
        public ICollection<Follow> Follows { get; set; }
    }

userPhotos class (I removed properties that aren't relevant in this example)

public class UserPhoto
{
    public int Id { get; set; }
    public string photoUrl { get; set; }
    public int UserId { get; set; }
}

This is the code that shows how it should work but it doesn't (this shows what I'm trying to accomplish)

    [HttpGet("u/{username}")]
    public async Task<IQueryable> GetSpecificFeed(string username)
    {
        var user = _context.Users.FirstOrDefault(x => x.Username == username);
        // Follower is the user that is being followed
        // Following is the user that is doing the following (so the current logged in user)

        var userId = user.Id;

        var photos = _context.UserPhotos;

        var followerIds = _context.Follows.Where(x => x.FollowerId == userId);
        // Get all of the userIds of the users that the currentUser is 
        // following and put them into 'followerIds'

        var feeds = photos.Where(x => x.UserId == followerIds);
        // Search 'photos' for where the 'userId (the id of the user who 
        //created it)' matches the 'followersIds'

        return feeds ;
    }

Upvotes: 0

Views: 205

Answers (2)

LouraQ
LouraQ

Reputation: 6891

You can refer to the following code:

    [HttpGet("u/{username}")]
    public async Task<IQueryable> GetSpecificFeed(string username)
    {
        var user = _context.Users.FirstOrDefault(x => x.Username == username);
        var userId = user.Id;
        var photos = _context.UserPhotos;
        var followerIds = _context.Follows.Where(x => x.FollowerId == userId).Select(x => x.FollowingId).ToList();
        var feeds = _context.UserPhotos.Where(x => followerIds.Contains(x.UserId));
        return feeds;
    }

Upvotes: 1

zekeriya kocairi
zekeriya kocairi

Reputation: 346

You should fire sql query first. You may use ToList() for that purpose in your case.

[HttpGet("u/{username}")]
public async Task<IQueryable> GetSpecificFeed(string username)
{
    var user = _context.Users.FirstOrDefault(x => x.Username == username);

    var userId = user.Id;

    var followerIds = _context.Follows.Where(x => x.FollowerId == userId).ToList();

    var feeds = _context.UserPhotos.Where(x => followerIds.Contains( x.UserId ));

    return feeds;
 }

Upvotes: 1

Related Questions