1

I'm able to fetch data from an API and insert them in the database with these two entities (two different API calls are used to fetch json data, then each element is stored in the database by creating an entity instance, then persisting them in the database).

class JiraTaskIssue
{
    private $id;

    private $issueId;

    private $issueKey;

    private $epicKey;

    private $summary;
}

The other entity

class JiraEpicIssue
{
    private $id;

    private $issueId;

    private $issueKey;

    private $firstRequestingCountry;
}

Then i used a custom sql script to merge these two tables with a left join. It works but i'm not allowed to use plain SQL on this project. I need to use the entities, repositories etc...

INSERT INTO jira_result (issue_id, issue_key, epic_key, summary, first_requesting_country)
SELECT 
    portal.jira_task_issue.issue_id, 
    portal.jira_task_issue.issue_key, 
    epic_key,
    summary,
    first_requesting_country  
FROM portal.jira_task_issue
LEFT OUTER JOIN portal.jira_epic_issue
ON portal.jira_task_issue.epic_key = portal.jira_epic_issue.issue_key;

I execute my current script like this

$em = $this->doctrine->getEntityManager();
$sql = file_get_contents($this->jiraScript);
$statement = $em->getConnection()->prepare($sql);
$statement->execute();

How can I create a method with doctrine to join these two tables thanks to the entities and insert the results in a new table (without this plain script) ?

(I simplified the entities a lot for the sake of lisibility)

Thomas Fournet
  • 660
  • 1
  • 6
  • 23
  • Possible duplicate of [Symfony 2: INNER JOIN on non related table with doctrine query builder](https://stackoverflow.com/questions/11116428/symfony-2-inner-join-on-non-related-table-with-doctrine-query-builder) – Jeroen Jul 03 '18 at 15:14
  • The valid answer doesn't really answers my question, I don't understand the point of inheritance mapping – Thomas Fournet Jul 03 '18 at 15:22
  • You might be able to create a DTO object when you use DQL to join the two entites on unrelated columns with the "WITH" clause: DTO doctrine https://www.doctrine-project.org/projects/doctrine-orm/en/2.6/reference/dql-doctrine-query-language.html#quot-new-quot-operator-syntax Not sure if it works for your usecase. – Jeroen Jul 04 '18 at 11:33

0 Answers0