79291

join tables and display result [Laravel 5]

Question:

I have groups table project_group pivot table questions table

groups table:

+----+--------+---------------------+---------------------+ | id | name | updated_at | created_at | +----+--------+---------------------+---------------------+ | 1 | GROUP1 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | | 2 | GROUP2 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | +----+--------+---------------------+---------------------+

pivot table project_group:

+-----+----------+------------+----------+---------+-----------+ | ids | group_id | project_id | admin_id | user_id | user_name | +-----+----------+------------+----------+---------+-----------+ | 4 | 115 | 1 | 1 | 3 | CLIENT | | 5 | 115 | 2 | 1 | 3 | CLIENT | +-----+----------+------------+----------+---------+-----------+

questions table:

+----+----------+---------------+---------------------+---------------------+ | id | group_id | text | updated_at | created_at +----+----------+---------------+---------------------+---------------------+ | 1 | 115 | PRVO PITANJE? | 2015-05-03 14:29:16 | 2015-05-03 14:29:16 | | 26 | 115 | DRUGO PITANJE? | 2015-05-06 00:44:02 | 0000-00-00 00:00:00 | +----+----------+---------------+---------------------+---------------------+

What I want is to display something like this:

<h2>GROUP 1</h2>

PRVO PITANJE?

DRUGO PITANJE?

BUT also to check if user 3 logged from pivot table and display that just for that user. I think it should be some JOIN project_group and question table and then to put where user_id = Atuh::id()

Any solution? :)

Answer1:

Firstly, your id on groups ought to align with group_id on project_group, right? So, I'll assume 'GROUP1' has id of 115 and not 1 as you have it.

Secondly, how do questions relate to projects? You have group_id on questions, which seems like it should be a project id, but this isn't certain.

SELECT text<br /> FROM questions<br /> INNER JOIN project_group USING (group_id)<br /> INNER JOIN groups ON project_group.project_id=groups.id<br /> WHERE groups.name='GROUP1'<br /> AND project_group.user_id=3;

Again, this will only work if you correct group_id to be a foreign key referencing id on the groups table. In this case, this should do what you want.

Recommend

  • Rails 3.2.3 namespaced controllers being overridden by global controllers with same name
  • Show project only if owner or member
  • laravel blade foreach fetching related tasks to projects 'Trying to get property of non-object&
  • SQLAlchemy | Limit join result to one row for one-to-many relationship
  • scala.collection.Seq doesn't work on Java
  • Extract string between xml tags in android without parsing the xml
  • Writing dataframe to postgres database
  • How to increase MongoDB performance
  • How do I write an item to a DynamoDb with the AWS DynamoDB DocumentClient?
  • How to lookup value with multiple criteria in excel 2007 and newer
  • How to Add Polymorphic Comments to Feed?
  • SF2 Functional tests : “Resetting the container is not allowed when a scope is active”
  • SQL - Select lowest values with group by and order by?
  • Flex items with same property values are rendering in different sizes
  • How to load more than one div at a time
  • Doctrine2 bulk import try to work with another entity
  • User messaging system
  • Linq Merge lists
  • Laravel: Getting Session ID oddly truncates when using foreach
  • cygwin cannot exec 'git-add--interactive' permission denied
  • Spring Cloud Microservice Architecture Confusion
  • Query to find the duplicates between the name and number in table
  • Grails calculated field in SQL
  • Transactional Create with Validation in ServiceStack Redis Client
  • Custom Tabgroup Appcelerator
  • Django: Count of Group Elements
  • Handling un-mapped Rest path
  • Linq Objects Group By & Sum
  • Read text file and split every line in MSBuild
  • PHP - How to update data to MySQL when click a radio button
  • How to make a tree having multiple type of nodes and each node can have multiple child nodes in java
  • How to add a column to a Pandas dataframe made of arrays of the n-preceding values of another column
  • To display the title for the current loaction in map in iphone
  • Web-crawler for facebook in python
  • Traverse Array and Display in markup
  • Unit Testing MVC Web Application in Visual Studio and Problem with QTAgent
  • trying to dynamically update Highchart column chart but series undefined
  • How to set the response of a form post action to a iframe source?
  • embed rChart in Markdown
  • java string with new operator and a literal