Request Object & Query OR Logic

Hey @Farry7!

Hmmm. I’m not familiar with this error! Is this from a custom project?

So, looking into this - https://stackoverflow.com/q... - it appears that this might happen if you have some sort of typo in your entity annotations. Unfortunately, the error isn’t very clear. But I would check your Annotations carefully to see if you can spot any issues!

Cheers!

Hey Chris!

This is just a helpful wrapper around $_GET super global variables, that except of getting a value may help you to provide a default value if the parameter key does not exist like:

$request->query->get('q', 'your-default-value-if-needed');

And except this get() method it also has getInt(), getBoolean(), has(), etc that allow you to write less custom code.

And this is just best practice to work with Request object instead of super global variables directly.

Cheers!

Hey Oliver,

Yeah, there's limited set of SQL functions that will work. Probably, the most easiest solution would be to search by time range, e.g. if you need to search all entries for today, September 1st - add a custom code before the query to know if you have a valid date or no, something like:

$startOfTheDay = new \DateTime();
$endOfTheDay = new \DateTime();
if (preg_match('/\d\d\d\d-\d\d-\d\d/', $term)) {
    // Looks like term is a date, create an object
    $startOfTheDay = new \DateTime($term.' 00:00:00');
    $endOfTheDay = new \DateTime($term.' 23:59:59');
}

$qb
    // ...
    ->andWhere('c.content LIKE :term OR c.authorName LIKE :term' OR (c.publishedAt >= :startOfTheDay AND c.publishedAt <= :endOfTheDay) ')

But probably for date/time ranges I'd recommend to add a new field for this. And yeah, it was just an example, you probably may want more complex or optimized regex pattern. And for operations with date I'd recommend Carbon library that makes it much easier: https://carbon.nesbot.com/docs/

I hope this helps!

Cheers!

Hey Brandon

Well done! You can watch this tutorial, it's unrelated but teaches to work with AJAX and forms https://symfonycasts.com/sc...
or you can watch our ReactJS tutorial if you want to dive deeper in the the Frontend world https://symfonycasts.com/sc...

Cheers!

Hey Virgile,

I'm not sure if we cover it somewhere, but I think this course might be interesting for you: https://symfonycasts.com/screencast/doctrine-queries - we cover how to write complex queries there.

But basically, you just need to write more complex andWhere() clause where you can combine a few simple clauses with OR condition. For example, something like this:

->andWhere('u.author IN (:authors)')
->setParameter('authors', ['Mike', 'Kenneth'])

You can also use our feature-rich search to find more ideas of how to write addWhere() conditions: https://symfonycasts.com/search?q=andWhere&sort=most relevant&page=1&types=code_block

For more complex things and better (relevant) search results you would need to use a special search engine like e.g. ElasticSearch that we use on SymfonyCasts. But we don't have a screencast about it. Thought we would like to record one in the future, but no any estimations yet.

I hope this helps!

Cheers!

It would really be great if you could make a Elasticsearch / some similar tutorial!
Ryan is such a profound teacher and this "feature", a "complete" search tutorial, is really missing for SF on this site.

At the moment, for my own project, I'am using "like" querys, because "match() against()" is slower.
But this "manual" approach is nothing compared to elastic search.
I would really appreciate a tutorial about it as well!

Hey Mike,

Thank you for your interest in SymfonyCasts tutorials! Yes, ElasticSearch is one of many tutorials we would like to release some day, but unfortunately we don't have any plans for it yet. Btw, could you share with us what exactly topics you would like to see in this course?

Cheers!

Great to hear that it’s on your todo!

1.) Best method of integrating it into Symfony (Have you used the FOS Bundle or did you “manually” integrate it into Symfonycasts website?)

2.) An advanced use case, that I implemented myself with “match against” and “member of” and would like to do so instead with elastic search:
How to filter many to many connections with it

By example:
A user wants to filter articles by categories, many categories can be selected and only articles get shown that are in *all* selected categories

Bonus:
The category filter multiselect element on the page gets updated to show only categories that have articles in it. By example: A website does have 3 categories (Earth, Mars, Sun) with one Article which is in the “Mars” and “Sun” category. A User now searches for all articles (“blank” search term) and filters the results by category “mars”. Now the website is only showing the “sun” filter as available filter. The “earth” filter is grayed out/disabled/hidden, because there would be no articles with “Mars” and “Earth”.

3.) Optional but interesting:

Scaling: How to scale elastic search instances for many users

If I can assist you in any another way I will be happy to do so :)

Hey Mike,

Thank you for sharing with us this! We will definitely consider this when start working on ElasticSearch course. But I don't have any estimations for you yet, it's not on our TODO list for the nearest future unfortunately. But a lot of new screencasts about Symfony 5 is coming ;)

Cheers!

Great! I want to suggest one topic for your SF5 course:
A search function based on user input (By example: Search in article content & article slugs for a $searchTerm (which can contain > 1 word))

The reasons why I think many others and I would profit big time if you would cover this topic:

1.) Since a "search bar" is so common nowadays, its a "generic" feature that a lot of people need & want to learn about
2.) I found out myself, that building it yourself, can be a real hassle.
a.) Its a "blind spot" on symfonycasts, since you don't have (or I have not seen it yet?) any course regarding a real "searchbar" (Like you said, Elasticsearch is a considered topic, but not in the nearest future)
b.) Easy solutions like querys with "like %$term%" are not recommended by all the tutorials I've found on google (performance killer). Statements like "Match() Against()" have to be implemented via Bundles (Beberlei Doctrine Extension), a mysql index have to be created via @Entity annotation and even if you do all this, you are not done, you have to escape the $searchTerm manually, because doctrine doesnt have a "build in" function (http://disq.us/p/27a2v1x)

Since Symfonycasts does have the highest quality and broadest selection of video tutorials for SF, I was surprised that you don't have tutorials for this kind of topic yet. (Or have I missed it?)

Thanks for your time SF Casts Team! :)

Hey Mike,

Ah, I see! Yeah, it might be tricky, we do cover some simple things with LIKE query in a few tutorials like:
https://symfonycasts.com/sc...
https://symfonycasts.com/sc...

Yes, using "LIKE" might be slow...but as you said "LIKE" is pretty simple to use. Well, first of all it's slow only on huge tables, and I bet if you would add indexes - it might be still a good speed for you, but of course you need to profile and see if it's ok or not for you. What about more complex solutions like using MATCH AGAINST - I will add this topic to our idea list, but I'm not sure it might be released soon as well.

Also, it's always a thin edge where you use simple "LIKE" and if you're not enough with it - usually devs move to specific solutions like ElasticSearch instead of doing something complex with MySQL abilities, first of all because of the speed. So really, if you're not enough that simple "LIKE" and you need something more complex for performing a search in a huge data set - I'd recommend you to look right into ElasticSearch or similar solutions instead of complicate things with SQL.

And probably for this same reason, I'm not sure if we would show something complex with MySQL other than "LIKE", we would better spend this time working on an advanced search solution like ElasticSearch. In other words, IMO ElasticSearch course has more chances to be released than a course about doing complex search natively in MySQL, that might just not worth it.

Anyway, thanks for sharing some more ideas on this topic! I'll try to discus this with the team as well.

Cheers!

It would be great to have some casts about ElasticSearch, actually, I'm quite surprised that in 2020 you don't have one yet.

Hey Juliano,

Agree, we have ElasticSearch course on our TODO list, but it's low priority for now unfortunately, so I don't have any estimations when it might be released. Btw, what topics exactly would you like to see in that course? Probably something that is not covered or covered badly in other tutorials over the internet? Actually, I believe there're some good courses on the internet about it. We don't want to make just "yet another ElasticSearch" course. If you may give us some good topics you would like to see in this course that you don't see in others - it would be awesome.

Cheers!

Hey Victor! Thanks for your answer. There are a quite few tutorials how to use Sf + ES, not so many. However, none of these go beyond the basics that you could find in the documentation. Other problem is that almost all these tutorials use the FOS bundle or other package, that unfortunately, are not updated and doesn't support last versions of ES nor Sf.
Also, another issue with most tutorials / packages, they are meant to use ES as a secondary DB, only for search purposes, nowadays isn't uncommon use only ES as your main DB.

I would argue that a good cast could cover these GAP's I mentioned before using the official Elasticsearch library and integrate it with Sf 5.

Hey Juliano,

Ah, I see. Thank you for sharing your thoughts on this topic! It's sad to hear bundles with ES are badly maintained :/ It's mostly driven by community, and it's pretty popular bundle. Not sure why it's so.

Anyway, I'll add your thoughts to our ES ticket, it will be definitely helpful when we will start working on this tutorial. Unfortunately, I don't have any estimations yet.

Cheers!

Hey Lijana Z.

Yeah, I don't like using expressions either, they are just hard to read (IMHO), but yes, you can add OR conditions inside an andWhere(). About auto adding "%" to a LIKE condition. I don't think that's a good idea because there are other kind of wildcards for a LIKE condition that you may want to use (https://www.w3schools.com/sql/sql_wildcards.asp )

Cheers!

Hey Dmitriy,

Good question! Actually, this's done by good powerful engines like ElasticSearch - you can google it. Unfortunately, we don't have a screencasts about it, though we plan to release one in the future. And ElasticSearch is just a one of many engines that can give you relevant results. But, fairly speaking, it's quite complex at the first sight, requires some work because you need to correctly index your website and, of course, requires some resources on your production server.

Btw, you can even enable google search on your website, though search results will be rendered in google styles and you'll have advert in the result output. If you don't want to see adverts - you have to pay for this.

If you don't need very cool search, or your users use the search rare on your website - probably you can try to do something similar yourself, for example, execute a few queries that first search more relevant results, like search by titles and headers, and another query that search for description and other minor things and then with PHP script do some merge, i.e. remove duplicated results, combine results correctly, etc. - well, it won't be perfect, but sometimes it might be enough.

Btw, you can try our search on SymfonyCasts - it's implemented with ElasticSearch :)

I hope it helps.

Cheers!

A ScreenCast about how to implement ElasticSearch into Symfony would be AWESOME +1

Hey Mike,

Yes, it's on our TODO list, we do really want to have it in the future but, unfortunately, I don't have any estimations yet when it might be released.

Cheers!

+1 To mike, that would be awesome indeed !

Hey Virgile,

Thank you for your interest in new SymfonyCasts courses! Your voice is counted :)

Cheers!

Hey Victor N.

If you inject your variables to the query using the QueryBuilder $qb->setParameter('name', $name);, then you are totally protected from SQL injection. You only have to be paranoid when you are doing it by your own (Executing a handmade query for example)

Cheers!

Hey Dirk,

Actually, you can use router to generate links with extra parameters, even if route does not have it - they will be added after question mark in your URL. So in controller:

$this->generateUrl('route_name', [
    'sortOrder' => 'DESC',
    'sortAttribute' => 'p',
]);

or in Twig templates:

{{ path('route_name', {'sortOrder' => 'DESC', 'sortAttribute' => 'p'}) }}

If you route has some required parameters - no problem, just pass required params first and then pass extra parameters, e.g.:

$this->generateUrl('article_show', [
    // List all required params first
    'slug' => 'article-slug',
    // Add any extra params
    'sortOrder' => 'DESC',
    'sortAttribute' => 'p.createdAt',
]);

So as you see you just need to pass actual parameters values, i.e. $sortAttribute and $sortOrder to your template. But you know what? You actually may use request object in Twig templates as well, so it would be something like:

{{ path('route_name', {'sortOrder' => app.request.query.get('sortOrder'), 'sortAttribute' => app.request.query.get('sortAttribute')}) }}

I hope I understand you right and it makes sense for you.

Cheers!

And is this the correct way for the "security check"?

CommentAdminController:

// Sort by
$sortBy = $request->query->get('s', 'DESC');

$queryBuilder = $repository->getWithSearchQueryBuilder($sortBy, $term);

CommentRepository:

$availableSortTerms = [
'ASC',
'DESC'
];

// Security check if orderBy exists
if (!in_array($orderBy, $availableSortTerms)) {
$orderBy = 'DESC';
}

...


return $qb
->orderBy('c.createdAt', $orderBy)
;

Would this be it?

Hey Mike,

It feels like a defensive programming, but probably a good idea, I like it!

Cheers!