Level 75
A - B - C - D ........
Is what I see on many sites. Where A is a link to plants starting with A, etc
1 like
Jun 13, 2020
12
Level 7
How to Create Sitemap for +30,000 records.
I am creating a manual sitemap for the website- https://www.nurserypeople.com/. I have a sitemap controller and it generates a xml sitemap for pages on the site based on records in the database.
The only issue is that one of my tables for plants, has over 30,000 records in it, and I can only call a couple thousand before I run out of memory.
Is there any way to build this out on one page, or is the only option doing something like /plants/first.xml /plants/second.xml and splitting the records out that way?
Level 88
You can automate this using a package like this one: https://github.com/spatie/laravel-sitemap
If you have memory issues it's better to fetch them in chunks or use a lazy load collection like that only fetches the record when it's needed. You can read more about that here: https://laravel.com/docs/7.x/collections#lazy-collections
1 like
Level 7
That is a great idea, @jlrdw Thanks!
Level 7
@bobbybouwmann yes, I tried the Spatie package, but unfortunately there are just too many records on the site. I think I will use the a, b, c answer with lazy collections. Thanks!
Level 7
@jlrdw Could I use route model binding for this in order to not have to create routes for each letter? I am guessing yes..
Level 23
you could create a command line command to run each night... and then cache the results... will these plants be changed/added constantly? that will tell u how frequent you should run thiscommand.
as for your q. you can create a route such as sitemap/{letter} and in controller do a sql to get all plants starting with that letter
Level 1
I also struggle with the same issues, how could you solve this problem? I tried using spatie/laravel-sitemap and crash the entire application because there are thousands of records. I still have no idea how could I approach this problem. Please help, thank you very much
1 like
Level 7
Hey @refojunior for sure! I will write a tutorial on how I did this, then send a link to you today. It is working well. I used the recommended way by @jlrdw but I will write more in detail how this is built in the tutorial.
Level 7
Hi @refojunior I wrote a tutorial on how to do this on my website: https://chriswray.dev/posts/how-to-create-a-sitemap-in-laravel-for-a-website-that-contains-thousands-of-records
Feel free to check it out and let me know if that helps you!
2 likes
Level 6
@cwray-tech I know this is old but this will not work and for anybody coming here this will be a miserection. In short what you have to do is generate a set amount and create many sitemaps. In this case you will want to use the chunk() function to deal with them. I feel that maybe going thought the alphabet is not a really good idea.
Posts::chunk(1000, function($posts) {
foreach ($posts as $post) {
// processing
}
});
- Take 1000 products and generate sitemap1.xml
- Take the next 1000 products and generate sitemap2.xml .... and so on till the end. Once at the end use the spatie or you can create your own index of all the sitemaps.
1 like
Level 7
@virgiltu VERY good comment. Thank you for sharing this. YEP, you are so right!! I'm going to try to update the blog eventually, but for now I will mark your answer as the best one.
Level 75
@cwray-tech you can simple paginate. If user wants plants starting with "c" then a query using like c% is used. Of course replace c with a variable. That way you can use normal pagination with say 20 per page.
You could even refine the "like" statement i.e., like ca%.
It doesn't sound like a you need a sitemap, rather a plant search. A sitemap would just have a link to "Plant Search"
Edit:
A sitemap is for showing (navigating to) parts of the site, not showing database records.
Please or to participate in this conversation.