[FIXED] How to Join and Display Data from two models on a single razor page (no mvc)

Issue

I have two models (Child and Shot) that I would like to join so that I can display information on a single razor page.

This small historical view app basically has client demographics in one model (Model Name is Child) and client immunizations in another table (Model Name is Shot). I have an index page that a user will click a 'View Immunization Details' details button next to a client's ID number and client name. This will take them to the page where I would like the joined data to display.

The top half of the page will display the demographics (ID, name, address, date of birth, etc) from the Child model and the lower half of the page will display a table of any immunization info for that particular client (from the Shot model). (immunization date, dose, shot description, etc)

This is a one to many relationship between Child and Shot. One Child record can have many Shot records.

In reviewing how to get two models to display on one page I have only seen where the data did NOT need to be filtered by the results from one model. As stated above, I need to show ONLY the shots that pertain to a SINGLE client on the Details page, NOT all shots from the Shot table.

Here is my code as I have it so far:

First, the Child model:

using System.ComponentModel.DataAnnotations;

namespace HealthyShots.Models
{
    public class Child
    {
        [Key]
        [Display(Name ="Child ID")]
        public int ChildId { get; set; }
        public List<Shot> Shots { get; set; }
        [Display(Name ="Last Name")]
        public string? LastName { get; set; }
        [Display(Name = "First Name")]
        public string? FirstName { get; set; }
        [Display(Name = "MI")]
        public string? MiddleInitial { get; set; }
        [Display(Name = "Full Name")]
        public string FullName
        {
            get
            {
                return LastName + ", " + FirstName + " " + MiddleInitial;
            }
        }
        [DataType(DataType.Date)]
        [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}")]
        [Display(Name = "Date of Birth")]
        public DateTime? BirthDate { get; set; }
        public string? Addr1 { get; set; }
        public string? Addr2 { get; set; }
        public string? City { get; set; }
        public string? State { get; set; }
        public string? Zip { get; set; }
    }
}

Then, the Shot model:

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace HealthyShots.Models
{
    public class Shot
    {
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        [Key]
        public int ShotId { get; set; }

        [Required]
        public int ChildId { get; set; }
        public Child Child { get; set; }

        [DataType(DataType.Date)]
        [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}")]
        [Display(Name = "Immunization Date")]
        public DateTime? Date { get; set; }
        public string? Dose { get; set; }
        [Display(Name = "Shot Number")]
        public int? ShotNo { get; set; }
        [Display(Name ="Shot Description")]
        public string? ShotDesc { get; set; }
    }
}

Then, my .cs (so far) for the Details razor page:

using HealthyShots.Data;
using HealthyShots.Models;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.EntityFrameworkCore;

namespace HealthyShots.Pages.Details
{
    public class DetailsModel : PageModel
    {
        private readonly ApplicationDbContext _db;

        public Child Child { get; set; }
        public Shot Shot { get; set; }
        

        public DetailsModel(ApplicationDbContext db)
        {
            _db = db;
        }
        
        public void OnGet(int Id)
        {
            _db.Child
                .Include(child => child.Shots)
                .FirstOrDefault(child => child.ChildId == Id);
        }

    }
}

And my razor view:

@page
@model HealthyShots.Pages.Details.DetailsModel

@{
    ViewData["Title"] = "Immunization Details";
}

<h2>Details</h2>

<div>
    <h4>Demographic Information</h4>
    <hr />

        <table class="table table-bordeless" style="width:100%">
            <tr>
                <td style="width: 10%">
                    <div class="mb-3">
                        <label asp-for="Child.ChildId"></label>
                        <input asp-for="Child.ChildId" disabled class="form-control"/>
                    </div>
                </td>
                <td style="width: 30%">
                    <div class="mb-3">
                        <label asp-for="Child.LastName"></label>
                        <input asp-for="Child.LastName" disabled class="form-control"/>
                    </div>
                </td>
                <td style="width: 30%">
                    <div class="mb-3">
                        <label asp-for="Child.FirstName"></label>
                        <input asp-for="Child.FirstName" class="form-control"/>
                    </div>
                </td>
                <td style="width: 5%">
                    <div class="mb-3">
                        <label asp-for="Child.MiddleInitial"></label>
                        <input asp-for="Child.MiddleInitial" disabled class="form-control"/>
                    </div>
                </td>
                <td style="width: 25%">
                    <div class="mb-3">
                        <label asp-for="Child.BirthDate"></label>
                        <input asp-for="Child.BirthDate" type="date" disabled class="form-control"/>
                    </div>
                </td>
            </tr>
        </table>
        <table class="table table-bordeless" style="width:100%">
            <tr>
                <td style="width: 25%">
                    <div class="mb-3">
                        <label asp-for="Child.Addr1"></label>
                        <input asp-for="Child.Addr1" disabled class="form-control"/>
                    </div>
                </td>
                <td style="width: 25%">
                    <div class="mb-3">
                        <label asp-for="Child.Addr2"></label>
                        <input asp-for="Child.Addr2" disabled class="form-control"/>
                    </div>
                </td>
                <td style="width: 25%">
                    <div class="mb-3">
                        <label asp-for="Child.City"></label>
                        <input asp-for="Child.City" disabled class="form-control"/>
                    </div>
                </td>
                <td style="width: 5%">
                    <div class="mb-3">
                        <label asp-for="Child.State"></label>
                        <input asp-for="Child.State" disabled class="form-control"/>
                    </div>
                </td>
                <td style="width: 20%">
                    <div class="mb-3">
                        <label asp-for="Child.Zip"></label>
                        <input asp-for="Child.Zip" disabled class="form-control"/>
                    </div>
                </td>
            </tr>
        </table>
<br />
<br />


    <h4>Immunizations</h4>
    <hr />

          <table class="table table-bordeless" style="width:100%">
            <tr>
                <td style="width: 20%">
                    <div class="mb-3">
                        <label asp-for="Shot.Date"></label>
                        <input asp-for="Shot.Date" type="date" disabled class="form-control"/>
                    </div>
                </td>
                <td style="width: 20%">
                    <div class="mb-3">
                        <label asp-for="Shot.Dose"></label>
                        <input asp-for="Shot.Dose" disabled class="form-control"/>
                    </div>
                </td>
                <td style="width: 60%">
                    <div class="mb-3">
                        <label asp-for="Shot.ShotDesc"></label>
                        <input asp-for="Shot.ShotDesc" disabled class="form-control"/>
                    </div>
                </td>
            </tr>
            }
        </table>

    <a asp-page="/Children/Index">Back to List</a>
</div>

I know I am missing code. I have googled and searched here for how the foreign key works, but still don't fully understand it. I realize that I have to tell Visual Studio that the Id (in the Child model) must be the same as the ChildId (in the Shot model), but I am not sure WHERE to do that or WHAT the correct syntax is.

I also need a wee bit of guidance on how to reference these items on the razor page. Once they are joined via foreign key, can I just access them with asp tag helpers like I would if it were a single model being referenced? Like I have done in my code so far?

Thanks in advance for any guidance you can provide. I am a beginner, so please understand that when you frame your answer.

Solution

Create relationships using conventions

In EF Core one way to define entity relationships is using conventions. This means you have to add specific properties with specific names to your entities so that EF understands the relationship between them.

Example:

public class Child
{
    public int ChildId { get; set; }

    public List<Shot> Shots { get; set; }
}

public class Shot
{
    public int ShotId { get; set; }

    public int ChildId { get; set; }
    public Child Child { get; set; }
}

In the above scenario EF will automatically create a 1 to many relationship between Shot and Child. One shot is linked to one child but one child can be linked to multiple shots. Shot.ChildId will be a foreign key to Child.ChildId.

https://www.learnentityframeworkcore.com/conventions/one-to-many-relationship

Loading related data from database

Now if you want to retrieve a specific child from database with all the related shots you can do:

var child = _db.Child
    .Include(child => child.Shots)
    .FirstOrDefault(child => child.ChildId == Id);

Include method tells EF to populate Child.Shots with all the shots that are related to this kid.

Similarly if you when you load the shots you can do:

var shots = _db.Shot
    .Include(shot => shot.Child)
    .ToList();

https://learn.microsoft.com/en-us/ef/core/querying/related-data/eager

Configure relationships manually

You can also configure relationships manually:

https://www.learnentityframeworkcore.com/configuration/one-to-many-relationship-configuration

Answered By - Dimitris Maragkos

Answer Checked By - Robin (PHPFixing Admin)

Read More

[FIXED] How to use Inner Join and Left Outer Join together?

Issue

I have two queries and I have to merge the second query into the 1st one such that it fetches the details of only those items that are currently present in the shelf( of all departments for that particular store Id).

Tables :

Item : item, uniqueLabel, ItemScale, ItemExpiry  // columns can have duplicates

Description : uniqueLabel, itemName //fields have unique value
Store :  StoreId, ShelfNo  // fields have unique values
Department : ShelfNo, ItemNo ( There can be duplicate items in a single Shelf)

I need to merge these two functionalities into one.

Query 1 to fetch details of items.

select item, Description.itemName, ItemScale, ItemExpiry 
from Item left outer join Description on (Item.uniqueLabel = Description.uniqueLabel)
where Item.uniqueLabel like '11%' //uniqueLabel starting from 11
order by Item.uniqueLabel

Query 2 to fetch details of all items that are in the Shelf(of all departments) for that store.

select itemNo from Store, Department
where Store.ShelfNo = Department.ShelfNo
and Department.itemNo is not null and Store.StoreId in('12345')

My Effort :

select itemNo, Description.itemName, ItemScale, ItemExpiry

from Item, Description, Department

where Department.itemNo = Item.item

Item.uniqueLabel = Description.uniqueLabel

where Item.uniqueLabel like '11%' 

But this is fetching a lot amount of data.

Solution

Your problem comes from trying to use the out-dated comma operator for your inner joins.

If you switch to the "modern" (standardised in 1992) JOIN syntax for Query 2, it will look something like this:

SELECT itemNo
FROM Store
INNER JOIN Department 
   ON Store.ShelfNo = Department.ShelfNo
WHERE Department.itemNo is not null 
   AND Store.StoreId in('12345')

You can then add in Item as a second INNER JOIN, relating it to the Department:

SELECT itemNo
FROM Store
INNER JOIN Department 
   ON Store.ShelfNo = Department.ShelfNo
INNER JOIN Item
   ON Department.itemNo = Item.item
WHERE Department.itemNo is not null 
   AND Store.StoreId in('12345')

And then add in your LEFT OUTER JOIN and extra WHERE condition from Query 1:

SELECT itemNo, Description.itemName, ItemScale, ItemExpiry
FROM Store
INNER JOIN Department 
   ON Store.ShelfNo = Department.ShelfNo
INNER JOIN Item
   ON Department.itemNo = Item.item
LEFT OUTER JOIN Description 
   ON Item.uniqueLabel = Description.uniqueLabel
WHERE Department.itemNo is not null 
   AND Store.StoreId in('12345')
   AND Item.uniqueLabel like '11%'

That query may not be exactly what you want, and some of the conditions are possibly redundant once the queries are merged, but hopefully this shows how the explicit join syntax can mix INNER and OUTER joins.

Answered By - IMSoP

Answer Checked By - Senaida (PHPFixing Volunteer)

Read More

[FIXED] How to use mysql JOIN without ON condition?

Issue

Is it possible to write join query without ON statement? and how do these joins differ LEFT JOIN, RIGHT JOIN works.

Solution

MySQL documentation covers this topic.

Here is a synopsis. When using join or inner join, the on condition is optional. This is different from the ANSI standard and different from almost any other database. The effect is a cross join. Similarly, you can use an on clause with cross join, which also differs from standard SQL.

A cross join creates a Cartesian product -- that is, every possible combination of 1 row from the first table and 1 row from the second. The cross join for a table with three rows ('a', 'b', and 'c') and a table with four rows (say 1, 2, 3, 4) would have 12 rows.

In practice, if you want to do a cross join, then use cross join:

from A cross join B

is much better than:

from A, B

and:

from A join B -- with no on clause

The on clause is required for a right or left outer join, so the discussion is not relevant for them.

Answered By - Gordon Linoff

Answer Checked By - Mary Flores (PHPFixing Volunteer)

Read More

[FIXED] How to use an UPDATE Query with an INNER JOIN to update fields within a table

Issue

I keep getting a run time error on the following SQL statement:

UPDATE tbl_1 
INNER JOIN tbl_2 ON tbl_1.PersNo = tbl_2.PersNo 
SET tbl_1.Marked = 'N/A' 
WHERE NOT EXISTS (SELECT * FROM tbl_2 WHERE tbl_1.PersNo = tbl_2.PersNo)

I think I may have some syntax backward, I'm looking to update the Table 1 Marked field with "N/A" (string value) when the PersNo does not exist in Table 2.

This all stems from a function with several SQL statments that allow me to Update the Table 1 Marked field with either "Yes", "No", or "N/A". So if there is a simpler way to do this, I'm open to suggestions.

In short, if the PersNo exists in Table 2 and the Type (from Table 2) is "Summary" then update Table 1 Marked field with "Yes", but is the Type (from Table 2) is "Full" then update Table 1 Marked field with "No", and is the PersNo does not exist in Table 2, update Table 1 Marked field with "N/A".

Solution

Your syntax is indeed incorrect for SQL Server - if I understand your last paragraph you just need a conditional case expression. If the following (of course untested) is not correct hopefully it's enough to put you on the right track:

update t1 set t1.Marked =
    case t2.type
      when 'Summary' then 'Yes'
      when 'Full' then 'No'
      else 'N/A'
    end
from tbl_1 t1
left join tbl_2 t2 on t1.PersNo = t2.PersNo;

Answered By - Stu

Answer Checked By - Marilyn (PHPFixing Volunteer)

Read More

[FIXED] How to combine 2 MySQL-functions

Issue

I have two SQL functions that I want to integrate in able to make a prestashop export with a single SQL query:

Function 1 joins data from different tables.
Function 2 converts multiple rows into a single row.

I am unable to have these functions work together... Let me describe the two functions.

FUNCTION 1

SELECT a.id_product, a.ean13, a.weight, b.id_product, b.name, c.id_product, c.id_tab, c.content
FROM ps_product AS a
INNER JOIN ps_product_lang AS b ON b.id_product = a.id_product
INNER JOIN ps_extraproducttab_product_lang AS c ON c.id_product = a.id_product

These INNER JOINS work fine:

+------------+---------------+-------------+-----------+--------+-------------------+
| id_product | ean13         |   weight    |   name    | id_tab |      content      |
+------------+---------------+-------------+-----------+--------+-------------------+
|         11 | 0000000000001 | 1000.000000 | product_A |      1 | some ingredients  |
|         11 | 0000000000001 | 1000.000000 | product_A |      2 | some allergenes   |
|         12 | 0000000000002 | 1500.000000 | product_B |      1 | other ingredients |
|         12 | 0000000000002 | 1500.000000 | product_B |      2 | other allergenes  |
+------------+---------------+-------------+-----------+--------+-------------------+

But I want to convert c somehow. The second INNER JOIN uses a table that has multiple rows on a single key (id_product):

+--------+------------+---------+-------------------+
| id_Tab | id_product | id_lang |      content      |
+--------+------------+---------+-------------------+
|      1 |         11 |       1 | some ingredients  |
|      2 |         11 |       1 | some allergenes   |
|      1 |         12 |       1 | other ingredients |
|      2 |         12 |       1 | other allergenes  |
+--------+------------+---------+-------------------+

I want to combine these rows first. Running this second function on table 'ps_extraproducttab_product_lang' does exactly that:

FUNCTION 2

SELECT t1.id_product, t1.content AS 'ingred', t2.content AS 'allerg'
FROM ps_extraproducttab_product_lang t1, ps_extraproducttab_product_lang t2
WHERE t1.id_product = t2.id_product
  AND t1.id_Tab = '1'
  AND t2.id_Tab = '2' 

It outputs:

+------------+-------------------+------------------+
| id_product | ingred            | allerg           |
+------------+-------------------+------------------+
|         11 | some ingredients  | some allergenes  |
|         12 | other ingredients | other allergenes |
+------------+-------------------+------------------+

I used this source, privided by Akina: https://dba.stackexchange.com/questions/236692/combining-multiple-rows-into-a-single-row-with-multiple-columns ( I still need to find out how to extend this code to a 3th and 4th id_Tab, although that is not the topic of my current question )

I am unable to integrate the above in a single query that would result into:

+------------+---------------+-------------+-----------+-------------------+-------------------+
| id_product | ean13         | weight      | name      | ingred            | allerg            |                  |
+------------+---------------+-------------+-----------+-------------------+-------------------+
|         11 | 0000000000001 | 1000.000000 | product_A | some ingredients  | some allergenes   |
|         12 | 0000000000002 | 1500.000000 | product_B | other ingredients | other allergenes  |
+------------+---------------+-------------+-----------+-------------------+-------------------+

How would you build a single SQL-query to get the above result?

Any help is appreciated!

Solution

Consider multiple CTEs if using latest versions of MySQL/MariaDB to your Prestashop platform. Be sure to use explicit joins (not implicit as DBA SE link uses) and avoid a, b, c table aliasing. Extend the self-joins to ps_extraproducttab_product_lang for the 3rd and 4th categories.

WITH ew AS
  (SELECT p.id_product, p.ean13, p.weight, pl.name
   FROM ps_product AS p
   INNER JOIN ps_product_lang AS pl
      ON p.id_product = pl.id_product
  ), ia AS 
  (SELECT t1.id_product, t1.content AS 'ingred', t2.content AS 'allerg' 
        , t3.content AS 'thirdcat', t4.content AS 'fourthcat'
   FROM ps_extraproducttab_product_lang t1
   INNER JOIN  ps_extraproducttab_product_lang t2
      ON t1.id_product = t2.id_product 
     AND t1.id_Tab = '1' AND t2.id_Tab = '2' 
   INNER JOIN  ps_extraproducttab_product_lang t3
      ON t1.id_product = t3.id_product AND t3.id_Tab = '3'
   INNER JOIN  ps_extraproducttab_product_lang t4
      ON t1.id_product = t4.id_product AND t4.id_Tab = '4'
  )

SELECT ew.id_product, ew.ean13, ew.weight, ew.name
     , ia.ingred, ia.allerg, ia.thirdcat, ia.fourthcat
FROM ew
INNER JOIN ia
   ON ew.id_product = ia.id_product

For earlier versions of MySQL (pre v8.0) or MariaDB (pre v10.2), use subqueries:

SELECT ew.id_product, ew.ean13, ew.weight, ew.name
     , ia.ingred, ia.allerg, ia.thirdcat, ia.fourthcat
FROM 
  (SELECT p.id_product, p.ean13, p.weight, pl.name
   FROM ps_product AS p
   INNER JOIN ps_product_lang AS pl
      ON p.id_product = pl.id_product
  ) ew
INNER JOIN 
  (SELECT t1.id_product, t1.content AS 'ingred', t2.content AS 'allerg'
        , t3.content AS 'thirdcat', t4.content AS 'fourthcat'
   FROM ps_extraproducttab_product_lang t1
   INNER JOIN  ps_extraproducttab_product_lang t2
      ON t1.id_product = t2.id_product 
     AND t1.id_Tab = '1' AND t2.id_Tab = '2' 
   INNER JOIN  ps_extraproducttab_product_lang t3
      ON t1.id_product = t3.id_product AND t3.id_Tab = '3'
   INNER JOIN  ps_extraproducttab_product_lang t4
      ON t1.id_product = t4.id_product AND t4.id_Tab = '4'     
  ) ia
   ON ew.id_product = ia.id_product

Answered By - Parfait

Answer Checked By - Mary Flores (PHPFixing Volunteer)

Read More

[FIXED] My INNER JOIN SQL command yields no results

Issue

I try to make a query using the INNER JOIN command and it executes successfully but it yields no results. Here is the SQL code I am using:

SELECT * FROM `departament` INNER JOIN `fakultet` 
ON "departament.#ID_Fakultet"="fakultet.ID_Fakultet"

And here are the tables the departament table:

the fakultet table

Solution

if you write in double (" ") or single (' ') quotes it will take as string.

SELECT * FROM `departament` INNER JOIN `fakultet` 
    ON departament.`#ID_Fakultet`=fakultet.`ID_Fakultet`

can you run this query?

Answered By - krishn Patel

Read More

[FIXED] Sql query select all messages from everybody inside every group where this one user is at

Issue

I'm trying to get all of the messages, from EVERY user, from 2 groups, where the user is located. But I don't know how to get all message from every group. This is my code so far:

SELECT DISTINCT m.*
FROM `message` m 
INNER JOIN users u
ON u.id = m.idUser
LEFT JOIN whats_app w
ON w.idUser= u.id 
WHERE u.id = w.idUser

So there is ONLY ONE user in 2 groups. I wan't to get all messages from everybody inside the groups where the ONE user is located at.

this is some simple sql query als example:

    create table users (
id                  int         PRIMARY KEY         NOT NULL,
name                varchar(60)
);

create table whatsapp(
idUser              ,
idGroup             int
);

create table allGroups(
id                  int         PRIMARY KEY         NOT NULL,
name                varchar(60)
);

create table message_send(
id                  int,
idUser              int,
message             text
);

INSERT INTO users(id, name) VALUES
(1, 'John'),
(2, 'Martijn'),
(3, 'Rick'),
(4, 'Vera'),
(5, 'Leon');

INSERT INTO allGroups(id, name) VALUES
(1, 'School'),
(2, 'Friends'),
(3, 'moreFriends'),
(4, 'secretmeeting');

INSERT INTO message_send(id, idUser, message) VALUES
(1, 2, 'How are you feeling today?'),
(2, 1, 'What up?'),
(3, 4, 'I am fine, you?'),
(4, 1, 'hi!');

Solution

create table message_send(
id                  int,
idUser              int,
idGroup             int,
message             text
);

Create message table like this and then just directly join with user and group you will get the output there is not need for the table watsapp

select  b.name,message 
from 
message_send as a,
users as b
where
a.idUser=b.id

Similarly join the group table

Answered By - ShivShankar Namdev

Read More