locked
Retrieving data from a join table RRS feed

  • Question

  • User986042657 posted

    I'll start off by saying I'm new at development and am working on building a project to learn on.  The project I have created has a couple of tables that I'm completely lost on how to retrieve data from through my API.  I have the following setup: 

    • Organization Table
    • Users Table
    • OrgToClient Table

    Each organization will have multiple users tied to it via the OrgToClient table.  I am able to assign users to organizations in the OrgToClient table just fine, this all works.  However, what I'm working on now is retrieving a list of users assigned to an organization.  I just cant seem to figure out how this will work.  

    Is anyone able to point me in the right direction or help me in doing this?  I'm not really sure where to start with it. Here are the relevant details for it:

    ClientRepository:

    using System.Collections.Generic;
    using System.Threading.Tasks;
    using Microsoft.EntityFrameworkCore;
    using Outmatch.API.Helpers;
    using Outmatch.API.Models;
    
    namespace Outmatch.API.Data
    {
        // Contains the concrete methods of interacting with the users table within the database, via the IClientRepository interface.
        public class ClientRepository : IClientRepository
        { 
    
            // Connect to the database using _context m
            private readonly DataContext _context;
            public ClientRepository(DataContext context)
            {
                _context = context;
    
            }
    
            // Add method for the users table
            public void Add<T>(T entity) where T : class
            {
                _context.Add(entity);
            }
    
            // Delete method for the users table
            public void Delete<T>(T entity) where T : class
            {
                _context.Remove(entity);
            }
    
            public async Task<OrgToClients> getClients(int userId, int OrganizationId)
            {
                return await _context.OrgToClients.FirstOrDefaultAsync(u => u.OrganizationId == OrganizationId && u.UserId == userId);
            }
    
            // Get a user method from the users table
            public async Task<User> GetUser(int id)
            {
                var user = await _context.Users.FirstOrDefaultAsync(u => u.Id == id);
                return user;
            }
    
            // Check if the user being assigned to an organization is already assigned to the organization    
            public async Task<OrgToClients> GetUserOrg(int userId, int organizationId)
            {
                return await _context.OrgToClients.FirstOrDefaultAsync(u => u.UserId == userId && u.OrganizationId == organizationId);
            }
    
            // Get (All users) from the users table
            public async Task<IEnumerable<User>> GetUsers()
            {
                var users = await _context.Users.ToListAsync();
    
                return users;
            }
            // Save changes to the users table
            public async Task<bool> SaveAll()
            {
                return await _context.SaveChangesAsync() > 0;
            }
        }
    }

    OrgToClient Model:

    namespace Outmatch.API.Models
    {
        public class OrgToClients
        {
            public int OrganizationId { get; set; }
            public int UserId { get; set; }
            public User User { get; set; }
            public Organizations Organization { get; set; }
        }
    }

    Users Model:

    using System;
    using System.Collections.Generic;
    using Microsoft.AspNetCore.Identity;
    
    namespace Outmatch.API.Models
    {
        // List of properties for the User (Client) table in the db
        public class User : IdentityUser<int>
        {
            public string FirstName { get; set; }
            public string LastName { get; set; }
            public DateTime ActiveDate { get; set; }
            public DateTime EndDate { get; set; }
            public virtual ICollection<UserRole> UserRoles { get; set; }
            public ICollection<OrgToClients> OrganizationId { get; set; }
        }
    }

    UsersController: 

    using System;
    using System.Collections.Generic;
    using System.Security.Claims;
    using System.Threading.Tasks;
    using AutoMapper;
    using Microsoft.AspNetCore.Authorization;
    using Microsoft.AspNetCore.Mvc;
    using Outmatch.API.Data;
    using Outmatch.API.Dtos;
    
    namespace Outmatch.API.Controllers
    {
        // API controller for the users table to get, modify, add and delete from the users table
        [Authorize]
        [Route("api/[controller]")]
        [ApiController]
        public class UsersController : ControllerBase
        {
            private readonly IClientRepository _repo;
            private readonly IMapper _mapper;
            public UsersController(IClientRepository repo, IMapper mapper)
            {
                _mapper = mapper;
                _repo = repo;
            }
    
            // Get a list of users from the user table
            [HttpGet]
            public async Task<IActionResult> GetUsers()
            {
                var users = await _repo.GetUsers();
    
                var usersToReturn = _mapper.Map<IEnumerable<UserForRegisterDto>>(users);
    
                return Ok(usersToReturn);
            }
    
            // Get individual users from the user table
            [HttpGet("{id}", Name = "GetUser")]
            public async Task<IActionResult> GetUser(int id)
            {
                var user = await _repo.GetUser(id);
    
                var userToReturn = _mapper.Map<UserForRegisterDto>(user);
    
                return Ok(userToReturn);
            }
    
            // Update a user's ddetails
            [Authorize(Policy = "RequireGlobalAdminRole")]
            [HttpPut("{id}")]
            public async Task<IActionResult> UpdateClient(int id, UserForEditDto userForEditDto)
            {
                var userRole = User.FindFirst(ClaimTypes.Role).ToString();
    
                if (userRole != "http://schemas.microsoft.com/ws/2008/06/identity/claims/role: GlobalAdmin")
                    return Unauthorized();
                
                var userFromRepo = await _repo.GetUser(id);
    
                _mapper.Map(userForEditDto, userFromRepo);
    
                if (await _repo.SaveAll())
                    return NoContent();
    
                throw new Exception($"Updating client with {id} failed to save");
            }
        }
    }

    I should also note, an error I have made is in my naming convention which I do need to fix.  Users and Clients are used interchangeably.  They will all soon be changed to Users.  

    Any assistance on this would be greatly appreciated!

    Thursday, January 30, 2020 6:00 AM

All replies

  • User61956409 posted

    Hi sapper6fd,

    what I'm working on now is retrieving a list of users assigned to an organization. 

    To achive it, your API controller action may need to accept organizationId as parameter, then you can retrieve all clients' Ids from OrgToClient based on organizationId that you passed , and then you can get details of users assigned to that specific organization from Users based on retrieved clients' Ids.

    With Regards,

    Fei Han

    Friday, January 31, 2020 2:37 AM