CODEWITHSUNDEEP
ormquery-buildertypeorm
user504909
user504909

Reputation: 9549

TypeORM do not select give any data from queryBuilder

I just want to select table 'person' innerjoin with 'contact' the code is:

const rep = (await getDatabaseConnection()).getRepository<Contact>('contact')
const build = rep.createQueryBuilder().innerJoin("person", "person").where("person.id= :personId", {personId})
console.log(build.getSql())
console.log(await build.getMany())
console.log(await build.getRawMany())

and the result is:

SELECT `Contact`.`id` AS `Contact_id`, `Contact`.`job_title` AS `Contact_job_title`, `Contact`.`email` AS `Contact_email`, `Contact`.`country` AS `Contact_country`, `Contact`.`state` AS `Contact_state`, `Contact`.`personId` AS `Contact_personId` FROM `contact` `Contact` INNER JOIN `person` `person` WHERE `person`.`id`= ?
[]
[]

but when I using the same SQL in database like:

SELECT `Contact`.`id` AS `Contact_id`, `Contact`.`job_title` AS `Contact_job_title`, `Contact`.`email` AS `Contact_email`, `Contact`.`country` AS `Contact_country`, `Contact`.`state` AS `Contact_state`, `Contact`.`personId` AS `Contact_personId` FROM `contact` `Contact` INNER JOIN `person` `person` WHERE `person`.`id`= '75c37eb9'

I can get the result:

"id"    "job_title" "email" "country"   "state" "personId"
"e27399a2-8822-4383-8ddb-9ef2a6030299"  "developer" "[email protected]"    "Australia" "NSW"   "75c37eb9"

why the QueryBuilder is empty result? How to write correct QueryBuilder for my case?

I change to:

    const build = rep.createQueryBuilder().innerJoin("person", "person").where({"person.id": personId})

It is still nothing in result.

the same thing happens:

    const build = rep.createQueryBuilder('contact').innerJoinAndSelect("contact.person", "person").where("person.id= :personId", {personId})
relations: ["person"]})
    console.log(build.getSql())
    console.log(await build.getMany())

the result is:

SELECT `contact`.`id` AS `contact_id`, `contact`.`job_title` AS `contact_job_title`, `contact`.`email` AS `contact_email`, `contact`.`country` AS `contact_country`, `contact`.`state` AS `contact_state`, `contact`.`personId` AS `contact_personId`, `person`.`id` AS `person_id`, `person`.`title` AS `person_title`, `person`.`first_name` AS `person_first_name`, `person`.`last_name` AS `person_last_name`, `person`.`expertise` AS `person_expertise`, `person`.`introduction` AS `person_introduction`, `person`.`COVID_19` AS `person_COVID_19`, `person`.`userId` AS `person_userId`, `person`.`belongOrganizationId` AS `person_belongOrganizationId` FROM `contact` `contact` INNER JOIN `person` `person` ON `person`.`id`=`contact`.`personId` WHERE `person`.`id`= ?
[]

the result is empty:

but if I using native sql:

SELECT `contact`.`id` AS `contact_id`, `contact`.`job_title` AS `contact_job_title`, `contact`.`email` AS `contact_email`, `contact`.`country` AS `contact_country`, `contact`.`state` AS `contact_state`, `contact`.`personId` AS `contact_personId`, `person`.`id` AS `person_id`, `person`.`title` AS `person_title`, `person`.`first_name` AS `person_first_name`, `person`.`last_name` AS `person_last_name`, `person`.`expertise` AS `person_expertise`, `person`.`introduction` AS `person_introduction`, `person`.`COVID_19` AS `person_COVID_19`, `person`.`userId` AS `person_userId`, `person`.`belongOrganizationId` AS `person_belongOrganizationId` FROM `contact` `contact` INNER JOIN `person` `person` ON `person`.`id`=`contact`.`personId` WHERE `person`.`id`='75c37eb9-1d88-4d0c-a927-1f9e3d909aef'

it will give me the result:

{
    "table": "UnknownTable",
    "rows":
    [
        {
            "contact_id": "e27399a2-8822-4383-8ddb-9ef2a6030299",
            "contact_job_title": "developer",
            "contact_email": "[email protected]",
            "contact_country": "Australia",
            "contact_state": "NSW",
            "contact_personId": "75c37eb9-1d88-4d0c-a927-1f9e3d909aef",
            "person_id": "75c37eb9-1d88-4d0c-a927-1f9e3d909aef",
            "person_title": "Mr.",
            "person_first_name": "sheng",
            "person_last_name": "lu",
            "person_expertise": "",
            "person_introduction": "input introduction",
            "person_COVID_19": 0,
            "person_userId": "be426167-f471-4092-80dc-7aef67f13bac",
            "person_belongOrganizationId": "06078ef6-619f-402f-aaf1-7db1c11de841"
        }
    ]
}

Upvotes: 0

Views: 2328

Answers (1)

Haresh Makwana
Haresh Makwana

Reputation: 230

You have to define person relation name in join like

const result = getRepository(Contact)
    .createQueryBuilder('contact')
    .innerJoinAndSelect("contact.person", "person")
    .where("person.id= :personId", {personId})

Here contact.person is the name of a relationship defined in the entity

Upvotes: 0

Related Questions