I have three tables
(id, firstname, lastname)
(id, name)
(id, u_id, m_id, secret, token)
(many-to-many. Represents modules that are authorized for users)What would be the query to get the following data back for a specific user:
(id, firstname, lastname)
(id, 1 as isAuthorized)
from users_modules(id, 0 as isAuthorized)
outer join with modules for the sake of me, couldn't figure this out. This is my try:
Select u.firstName, u.lastName, u.id, m.id,m.name
From modules m
Left Outer Join users_modules uc On uc.m_id = m.id
Left Outer Join users u On
u.id = uc.u_id And
u.id = 120
Then I want to map the results back to my User
class using dapper so that user
object will have all modules
authorized and unauthorized in Modules
list property
public class User
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public List<Module> Modules{ get; set; }
public User()
{
Modules= new List<Module>();
}
}
[Table("modules")]
public class Module
{
public int Id { get; set; }
public string Name { get; set; }
public string AuthorizationBaseUri { get; set; }
public bool IsAuthorized { get; set; }
public string LoginUrl { get; set; }
public Module()
{
LoginUrl = string.Empty;
AuthorizationBaseUri = string.Empty;
IsAuthorized = false;
}
}
I had to get the authorized modules for a user first (using dapper multiple mapping), then get all modules, and then finally do the subtraction manually at my business layer. I know there is a smarter way to do it, yet to find out.
Try this:
select u_m.id,firstname,lastname,m_id, secret, token,name from
(select u.id,firstname,lastname,m_id, secret, token from
(select id, firstname, lastname from users where id = 120) as u
left join
(select id, u_id, m_id, secret, token from users_modules) as um
on u.id = um.u_id) as u_m
left join
(select id,name from modules) as m
on m.id = u_m.m_id