Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

azbx's avatar
azbx
OP
3 years ago
Level 17

PHP SQL Conversion to ELOQUENT

I would like some feedback on this

SELECT User.*, UserGame.ID AS GameID, UserGame.Name AS GameName, (SELECT SUM(Recurring) FROM UserProfileView WHERE TargetID = User.ID) AS ProfileViews, (SELECT COUNT(*) FROM BlockedUser WHERE (RequesterID = User.ID AND BlockedID = ".$myU->ID.") OR (RequesterID = ".$myU->ID." AND BlockedID = User.ID)) AS BlockedStatus, (SELECT COUNT(*) FROM UserInventory JOIN Item ON UserInventory.ItemID = Item.ID WHERE (Item.IsCollectible = 1 OR Item.ItemType = 7) AND Item.TradeLock = 0 AND UserInventory.UserID = ".$myU->ID." AND UserInventory.CanTrade = 1) AS MyNumCollectibles, (SELECT COUNT(*) FROM UserInventory JOIN Item ON UserInventory.ItemID = Item.ID WHERE (Item.IsCollectible = 1 OR Item.ItemType = 7) AND Item.TradeLock = 0 AND UserInventory.UserID = User.ID AND UserInventory.CanTrade = 1) AS UserNumCollectibles, UserGroup.ID AS FavoriteGroupID, UserGroup.Name AS FavoriteGroupName, UserGroup.SEOName AS FavoriteGroupSEOName, UserGroup.LogoStatus AS FavoriteGroupLogoStatus, UserGroup.LogoImage AS FavoriteGroupLogoImage, UserGroup.IsVerified AS FavoriteGroupIsVerified FROM User LEFT JOIN game.UserGame ON UserGame.ID = User.InGameId LEFT JOIN UserGroup ON User.FavoriteGroup = UserGroup.ID WHERE User.Username = ?

How would I use eloquent to translate this into useable laravel MySQL code without using raw statements? I am moving a project from PHP to laravel.

0 likes
3 replies
tisuchi's avatar

@[aeo] Sorry to say, it's really hard to convert your sql query to eloquent because it's not readable.

What I suggest instead you establish some relationship and then fetch the query based on that. Here is what I can extract from your query.

  • One-to-many relationship between User and UserGame
  • One-to-many relationship between User and UserGroup
  • Many-to-many relationship between User and BlockedUser
  • Many-to-many relationship between User and UserInventory

⚠️ May be I am wrong. You may need to tweak some places.

1 like

Please or to participate in this conversation.