Single MySQL Query one-to-many efficiency
I am fairly inexperienced with the ramifications of doing a single select where joining several tables and there are several one-to-many relationships while also running some built in MySQL functions using column values between tables. The tables will be fairly large: 10,000 records in the accounts table, 70,0000 records in the locations table, 350,000 in the email_sequences table. The select I am performing: (from Laravel’s query builder)
$interval = $getSheduleInterval($current); $sql = "SELECT seq.id FROM locations AS loc JOIN accounts as acc ON acc.id=loc.account_id JOIN email_sequence AS seq ON loc.id=seq.location_id WHERE acc.suspended = 0 AND acc.deleted_at IS NULL AND loc.active = 1 AND seq.immediate = 0 AND TIME(CONVERT_TZ(seq.schedule_time, 'UTC', loc.timezone)) >= start> AND TIME(CONVERT_TZ(seq.schedule_time, 'UTC', loc.timezone)) < end>"; $sequents = DB::select($sql); I have seen (read) where in some instances it is more efficient to perform several queries and process the result of each in between rather than a complex SELECT query where several one-to-many or many-to-one relationships are involved. Are there going to be any major issues with this single select as I have it currently constructed? Edit: As requested here is the $getScheduleInterval() code;
// @param object [Takes a instance of Carbon::now()] public function getScheduleInterval($carbonObj)< $interval = (object)[]; // round time down to quarter hour $carbonObj->minute = $carbonObj->minute - ($carbonObj->minute % 15); $carbonObj->second = 00; //set start / end $interval->start = "'toTimeString()>'"; $interval->end = "'addMinutes(15)->toTimeString()>'"; return $interval; > Php on to one relationship sql
Foreign key is for table column screenshot I want to output the following fields: column from table all rows from table all rows from table. You’ll need to basically join your tables together so you can link the data you need from table to table, and then add a where clause for your desired condition.
ORM Relationships, PHP and SQL Join
I’m working on a little PHP Framework for a school project and I’m currently learning how to build the ORM part.
However, I can’t figure out how to map object relationships from a SQL query with joins or I just don’t know the right words to search 🙁
I tried to do a simple : SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.t1_id
What I get is a simple array with all the fields from both tables and id column gets overridden as It exist in both.
[ "id" => "2" "title" => "Lorem" "text" => "Ipsum" "name" => "Tomato" "description" => "Tomato are nice" ] So my question is there a simple way to get something like this with joins ?
[ "t1" => [ "id" => 2 "title" => "Lorem" "text" => "Tomato" "t2" => [ "id" => 3 "name" => "Tomato" "description" => "Tomato are nice" ] ] No, a join is for creating a table view of the two tables side to side. But you can do:
SELECT t1.id, t1.title, t1.next, t2.name as "t2_name", t2.description as "t2_description" FROM t1 LEFT JOIN t2 ON t1.id = t2.t1_id Which will give you prefixes like:
[ "t1" => [ "id" => 2 "title" => "Lorem" "text" => "Tomato" "id" => 3 "t2_name" => "Tomato" "t2_description" => "Tomato are nice" ] ] That solves the same problem you were trying to solve.
How to handle one to many relation in PHP, Usually its not PHP that has to handle it, you use a JOIN in your SQL query and MySQL provides you with a promptly associated result. Thats the whole point of using a DBMS, its so you don’t have to code that stuff yourself. –
How to select one to many relationship data in PHP PDO
I have total 3 tables in PHPMyAdmin:
I want to output the following fields:
- movie_title column from tmdb_movies table
- all genres_name rows from genres table
- all cast_name rows from cast table.
What is the smallest command to select and echo data? I am not familiar with the join command in SQL.
prepare("SELECT tmdb_movies.*, GROUP_CONCAT(genres_name) as genres_name FROM tmdb_movies JOIN genres ON tmdb_movies.tmdb_id=genres.cast_tmdb_id GROUP BY tmdb_movies.cast_tmdb_id, tmdb_movies.tmdb_id, tmdb_movies.movie_title"); // Then fire it up $stmt->execute(); // Pick up the result as an array $result = $stmt->fetchAll(); // Now you run through this array in many ways, for example foreach ($result as $row) < print "".$row["movie_title"]." ".$row["genres_name"] ." ".$row["cast_name"] ."
"; > ?> SELECT tmdb_movies.movie_title, GROUP_CONCAT(DISTINCT genres.genres_name SEPARATOR ', ') AS genres_names, GROUP_CONCAT(DISTINCT cast.cast_name SEPARATOR ', ') AS cast_name FROM tmdb_movies JOIN genres ON tmdb_movies.tmbd_id=genres.genres_tmdb_id JOIN cast ON cast.cast_tmdb_id=tmdb_movies.tmbd_id GROUP BY tmdb_movies.movie_title ORDER BY tmdb_movies.movie_title GROUP_CONCAT => Concat fields from different row in one field
JOIN => you make two table correspond by their key
GROUP BY => group data per those fields
I’ve done the query I could without actually seeing your tables, so you might want to adapt it if it doesn’t work on first try.
Php — unidirectional vs bidirectional in one-to-one, 1 Answer. Sorted by: 3. In general, you can use both unidirectional or bidirectional relationships whenever you want. You design your code due to the use you will do of it. That is, for example, if you are declaring an unidirectional relationship oneToOne is because you only need to have the information in one …
MySQL and PHP Many-to-Many Database Relationships
I’m trying to advance my knowledge of SQL queries but am getting stuck on a many-to-many relationship query.
Using PHP and MySQL database structure is as follows:
-------------------------------------------- | colour | -------------------------------------------- | colour_id | colour | -------------------------------------------- | 1 | blue | -------------------------------------------- | 2 | red | -------------------------------------------- ############################################ -------------------------------------------- ----------------------------------------------------------------- | product_colours | ----------------------------------------------------------------- | id | product_id | colour_id | ----------------------------------------------------------------- | 1 | 1 | 2 | ----------------------------------------------------------------- | 2 | 2 | 1 | ----------------------------------------------------------------- ################################################################# ----------------------------------------------------------------- ----------------------------------------------------------------- | products | ----------------------------------------------------------------- | id | name | details | ----------------------------------------------------------------- | 1 | product 1 | blah | ----------------------------------------------------------------- | 2 | product 2 | blah | ----------------------------------------------------------------- ################################################################# ----------------------------------------------------------------- ----------------------------------------------------------------- | product_group_names | ----------------------------------------------------------------- | id | product_id | group_name_id | ----------------------------------------------------------------- | 1 | 1 | 1 | ----------------------------------------------------------------- | 2 | 2 | 2 | ----------------------------------------------------------------- ################################################################# ----------------------------------------------------------------- -------------------------------------------- | group_name | -------------------------------------------- | group_name_id | group_name | -------------------------------------------- | 1 | product_group_1 | -------------------------------------------- | 2 | product_group_2 | -------------------------------------------- ############################################ -------------------------------------------- Could I write one query using joins which says: SELECT * colours WHERE group_name = product_group_1 ?
Any help would be amazing. Thank you so much.
You can certainly write a query to do that. You’ll need to basically join your tables together so you can link the data you need from table to table, and then add a where clause for your desired condition.
Your colours are «linked» to your product_colours via the colour_id attribute, the product colours are linked to the product_group_names via the product_id attributes on both tables, and your group names are linked via the group_name_id and group_id attributes on the group_names table.
When you’re doing this kind of query though, if you have a lot of records in these tables — the queries can go very slow. You need to make sure you add indicies on each of the columns referenced in the JOIN. ON clauses, as well as the WHERE clause.
SELECT colour FROM colours c JOIN product_colours pc ON c.id = pc.colour_id JOIN product_group_names pgn ON pgn.product_id = pc.product_id JOIN group_name gn ON gn.id = pgn.group_name_id WHERE gn.group_name = "product_group_1" Php — How to create one to many relationship between, CREATE TABLE email ( emailid int not null auto_increment, emailaddress varchar (100), personid int, primary key (emailid), foreign key (personid) references person (personid) ) This would buy you a one to many relationship — where one person can have zero to many email addresses (each …
Select sql many-to-many relationship into nested php object
I have two data tables SEQUENCES and ORGANISMS whose many-to-many-relationship is mappend in the table SOURCES . There is also a 1-m relationshipt between SOURCES and ENTRIES . I will append a detailed structure.
What i want to achieve, is the display of all sequences with all associated organisms and entries, where a condition within the sequences table is met. I have some ideas on how to achieve this, but i need the solution with the best performance, as each of these contains 50k+ entries.
Select all organisms that belong to the same sequence as a concatenated string in sql, and split it in PHP. I have no idea though, how to do the concatenation in SQL.
select same sequences with different organisms as distinct records, order by organism, and join them later in php. though this somehow feels just wrong.
use views. ANY idea on this one appreciated
SEQUENCES SEQUENCE_ID DESCRIPTION ORGANISMS ORGANISM_ID NAME SOURCES SOURCE_ID SEQUENCE_ID FK to SEQUENCES.SEQUENCE_ID ORGANISM_ID FK to ORGANISMS.ORGANISM_ID ENTRIES SOURCE_ID FK to SOURCES.SOURCE_ID ENTRY_VALUE desired outcome
array( array( "SEQUENCE_ID" => 4, "DESCRIPTION" => "Some sequence", "SOURCES" => array( array( "ORGANISM_ID" => 562, "ORGANISM_NAME" => "Escherichia coli", "ENTRIES" => array( "some entry", "some other entry" ), array( "ORGANISM_ID" => 402764, "ORGANISM_NAME" => "Aranicola sp. EP18", "ENTRIES" => array() ) ) ), array( "SEQUENCE_ID" => 5, . ) ) PHP5 and FIREBIRD2.5.1
You can’t fetch a nested array like that directly from a flat table structure. But if I get you right, what you want to do is not that hard to achieve.
I don’t understand why you would concatenate things and then split them again, that’s hard to maintain and probably slow.
I see two approaches here:
- Fetch everything at once as flat table using JOIN and loop through it in PHP. This approach creates a lot of duplication but it’s fast because you can fetch all data in one query and then process it with PHP.
- Fetch every entity separately, loop and fetch the next hierarchy level as you go. This approach will be slower. It takes complexity away from the SQL query and doesn’t fetch redunant data. It also gives you more freedom as to how you loop through your data and what you do with it.
Alternatively you might want to actually store hierarchical data in a no-sql way, where you could already store the array structure you mentioned.
How to Create a real one-to-one relationship in SQL Server, I’m pretty sure it is technically impossible in SQL Server to have a True 1 to 1 relationship, as that would mean you would have to insert both records at the same time (otherwise you’d get a constraint error on insert), in both tables, with both tables having a foreign key relationship to each other.