1

DATABASE:

id | name     | // ...
====================
1  | London   |
2  | Paris    |
3  | Moscow   |
4  | New York |

// ...

TEXT:

This is a big city. I live in this town.
I like the name of city. 'New York' is very cool.
However, I have to go to Moscow this summer.
// ...

There are a lot of records in the database.
Text is written in various languages​​.

I would like to get the records related to text.
In this case, I would like to get the records of 'New York' and 'Moscow'.

I am using a Doctrine2 ORM and DQL.
And my database is usually mysql.

Is it possible to achieve this by using the DQL?

UPDATE

City Entity : id, name, population date_created etc...

id | name     | population | // ...
====================================
1  | London   | 1,2448,3938| 
2  | Paris    | 1,8759,4844|
3  | Moscow   |12,8450,3748|
4  | New York | 8,4795,8558|

// ...

Article Entity : id, body, author, date_created etc...

id | body                                        | // ...
============================================================
1  | This is a big city. I live in this town.    |
   | I like the name of city.                    |
   | 'New York' is very cool.                    |
   | However, I have to go to Moscow this summer.|
   | // ...                                      | 
2  | We bought a house in London.  //...         |
3  | I go to Canada this weekend.  //...         |
4  | Weather in Africa today is too bad. //...   |

// ...

The text is good whether it get from a file or a database.

public function findOneById($id)
{
    $query = $this->getEntityManager()->createQuery('
        SELECT a, u
        FROM MyArticleBundle:Article a
        LEFT JOIN a.author u
        WHERE a.id = :id
    ')
    ->setParameter('id', $id);

    return $this->try_catch($query);
}

or

$article = file_get_contents('example.txt');

And I bring it to the display.

{{ article }} or {{ article.body }} etc...

At this time, how do I get the city data of related to this article?
Is it'll need a complex database configuration?

The text and entity (in this case 'city' data) has no association.
Is it possible that I get the data that appears in the text or strings?

such as : SELECT c FROM MyExampleBundle:City c WHERE c.name appear in ({$text});

Although I know the example is impossible, but I wanted to know whether there is a way to easily get like a that in DQL.

R.Atim
  • 367
  • 1
  • 7
  • 16
  • Do you want to get the records where the `name` or `text` field contains 'New York' and 'Moscow'? Please add more information about your entity, we can't build a query without the name of the entity. – A.L Apr 14 '14 at 16:39
  • Thank you for the comment. I added the description above. – R.Atim Apr 14 '14 at 19:02
  • Is the first part of your question still unanswered? I suggest to move the last question to a new question. And it's hard to answer to the last question without information about how the entities are associated. – A.L Apr 14 '14 at 19:12
  • Sorry my english and I can't figure out now if it will need a complex configuration of database. My question is simple. The text and entity (in this case 'city' data) has no association. Is it possible that I get the data that appears in the text or strings? such as : `where city.name in ({$text})`. Although I know the example is impossible, but I wanted to know whether there is a way to easily get like a that in DQL. – R.Atim Apr 14 '14 at 19:52
  • Please read your question again, it's hard to understand what is the point of your question. See the [doctrine2 documentation](http://docs.doctrine-project.org/en/latest/reference/association-mapping.html) about association to associate your two entities. – A.L Apr 15 '14 at 13:33

1 Answers1

0

In this case, I would like to get the records of 'New York' and 'Moscow'.

So you need to use the like() function with the % character. The % will act as a wildcard, for example A% will select every row where the selected field starts with A. And %A% will select every row which contains A.

public function findArticlesFromTowns($town_1, $town_2)
{
    $qb = $this->createQueryBuilder('u');

    $buffer = $qb
        ->select('a, u')
        ->leftJoin('a.author', 'u')
        ->where(
            $qb->expr()->andX(
                $qb->expr()->like('a.body',
                    $qb->expr()->literal('%'.$town_1.'%')),
                $qb->expr()->like('a.body',
                    $qb->expr()->literal('%'.$town_2.'%'))
            )
        )
    ;

    $q = $buffer->getQuery();

    return $q->getResult();
}

$town_1 and $town_2 are New York and Moscow respectively. By using andX() the query will select all the rows where the body field contains New York and Moscow.

A.L
  • 10,259
  • 10
  • 67
  • 98
  • No no no... my english is too bad? uh... in this case, I do not know where the city is included in the text. My request the point is that I would like to get the city data that appear in the text that I don't know the content. This is an image : `SELECT c FROM MyExampleBundle:City c WHERE c.name appear in ({$text});` – R.Atim Apr 14 '14 at 20:57
  • The position of the towns names is not a problem, the query will fetch the rows which *contains* the towns names, they can be anywhere in the text. – A.L Apr 14 '14 at 21:02
  • Your answer is I know what the city name is included in the text. I can do it if such is the case. I don't want to get the article data that contain the specific city name. I'd like to get the city data that the name of the city is included in the text. I don't know the content of text. I don't know which city name included to the text. – R.Atim Apr 14 '14 at 21:51
  • I quoted a sentence from your question and I think that my answer is correct. Why ask for *New York* and *Moscow* if you don't know the city names? – A.L Apr 15 '14 at 13:29